SQL > SQL Commands > Update Statement

Once there's data in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command. The syntax for this is

UPDATE "table_name"
SET "column_1" = [new value]
WHERE "condition";

For example, say we have a table as below:

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

and we notice that the sales for Los Angeles on Jan-08-1999 is actually $500 instead of $300, and that particular entry needs to be updated. To do so, we use the following SQL query:

UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';

The resulting table would look like

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles500Jan-08-1999
Boston700Jan-08-1999

In this case, there is only one row that satisfies the condition in the WHERE clause. If there are multiple rows that satisfy the condition, all of them will be modified. If no WHERE clause is specified, all rows will be modified.

Update Multiple Columns

It is also possible to UPDATE multiple columns at the same time. The syntax in this case would look like the following:

UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE "condition";

For example, let's say we notice that the 'San Diego' entry has the wrong Sales and TXN_Date information. To fix it, we run the following SQL statement:

UPDATE Store_Information
SET Sales = 600, Txn_Date = 'Jan-15-1999'
WHERE Store_Name = 'San Diego';

The table now becomes,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego600Jan-15-1999
Los Angeles500Jan-08-1999
Boston700Jan-08-1999

IMPORTANT: When using the UPDATE statement, pay special attention to make sure that condition is specified. Otherwise, the value of all rows can be changed.

Next: SQL DELETE FROM




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy