UPDATE statement is used to modify data of one or more columns in one or more rows of a table. The SET clause indicates which columns to modify and the values they should be given.
The WHERE clause defines which rows are updated by the command. In the absence of WHERE clause, all the rows for the specified table are updated.
SYNTAX I (Without WHERE Clause):
UPDATE Table1
SET Column1 = Value1
EXAMPLE:
UPDATE Product
SET Price = Price * 2;
The above query will update all the rows in the Product table and set the Price as double the current value.
SYNTAX II (With WHERE Clause):
UPDATE Table1
SET Column1 = Value1
WHERE [Condition]
EXAMPLE:
UPDATE Product
SET Price = Price * 2;
WHERE ProductName = 'XBOX'
The above query includes a WHERE clause and will only update specific rows in the Product table which satisfies the WHERE condition.
SYNTAX III (With multiple columns):
UPDATE Table1
SET Column1 = Value1, Column2 = Value2 …
WHERE [Condition]
EXAMPLE:
UPDATE Product
SET Price = Price * 2, ProductName = 'XBOX ONE'
WHERE ProductName = 'XBOX'
The above query updates columns Price and ProductName in the Product table for rows satisfying the WHERE condition.
SYNTAX IV (With multiple tables):
UPDATE Table1
SET Table1.Column1 = Value1
FROM Table1 INNER JOIIN Table2 ON Table1.Column = Table2.Column
WHERE [Condition]
EXAMPLE:
UPDATE P
SET P.Price = Price * 2
FROM Product AS P
INNER JOIN Location AS L ON P.LocationID = L.LocationID
WHERE P.ProductName = 'XBOX'
AND L.LocationName = 'San Francisco'
The above query updates data in the target table based on information from another table.