The SQL UPDATE Statement

The SQL UPDATE Statement is used to modify the existing records in a table. This statement is a part of Data Manipulation Language (DML), as it only modifies the data present in a table without affecting the table”s structure.

To filter records that needs to be modified, you can use a WHERE clause with UPDATE statement. Using a WHERE clause, you can either update a single row or multiple rows.

Since it only interacts with the data of a table, the SQL UPDATE statement needs to used cautiously. If the rows to be modified aren”t selected properly, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted.

The SQL UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

Syntax

The basic syntax of the SQL UPDATE statement with a WHERE clause is as follows −

UPDATE table_name
SET column1 = value1, column2 = value2,..., columnN = valueN
WHERE [condition];

You can combine N number of conditions using the AND or the OR operators.

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as −

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS SET ADDRESS = ''Pune'' WHERE ID = 6;

Output

The query produces the following output −

Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS WHERE ID=6;

Now, the CUSTOMERS table would have the following records −

Update Multiple ROWS and COLUMNS

Using SQL UPDATE statement, multiple rows and columns in a table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it.

However, to update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.

Syntax

Following is the syntax to update multiple rows and columns −

UPDATE table_name
SET column_name1 = new_value, column_name2 = new_value...
WHERE condition(s)

Example

If you want to modify all the AGE and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough. Following query increases the age of all the customers by 5 years and adds 3000 to all the salary values −

UPDATE CUSTOMERS SET AGE = AGE+5, SALARY = SALARY+3000;

Output

The query produces the following output −

Query OK, 7 rows affected (0.12 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

Example

But, if you want to modify the ADDRESS and the SALARY columns of selected records in the CUSTOMERS table, you need to specify a condition to filter the records to be modified, using the WHERE clause, as shown in the following query −

UPDATE CUSTOMERS 
SET ADDRESS = ''Pune'', SALARY = 1000.00 
WHERE NAME = ''Ramesh

Output

This query produces the following output −

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS WHERE NAME = ''Ramesh

Now, CUSTOMERS table would have the following records −