SQL > SQL Commands > Update Statement

The UPDATE statement is used to modify data in a database table.

Syntax

UPDATE can be used to modify one column at a time or multiple columns at a time. The syntax for updating a single column is as follows:

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

The syntax for updating multiple columns is as follows:

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

Examples

We use the following table for our examples.

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  250  Jan-07-1999 
 Los Angeles  300  Jan-08-1999 
 Boston  700  Jan-08-1999 

Example 1: Update a single column

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_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  250  Jan-07-1999 
 Los Angeles  500  Jan-08-1999 
 Boston  700  Jan-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.

Example 2: Update multiple columns

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_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  600  Jan-15-1999 
 Los Angeles  500  Jan-08-1999 
 Boston  700  Jan-08-1999 

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

Exercises

1. Using the same Store_Information table right above, what data is in the table after the following SQL statement is executed?
UPDATE Store_Information
SET Sales = 800
WHERE Store_Name = 'Boston';

2. Continuing to use the same table. What is the content of the table after the following SQL statement is executed?
UPDATE Store_Information
SET Sales = 2000
WHERE Store_Name = 'Los Angeles' AND Txn_Date = 'Jan-10-1999';

3. Again using the same table. What is the content of the table after the following SQL statement is executed?
UPDATE Store_Information
SET Sales = 1000;

Next: SQL DELETE FROM

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact