UPDATE command in SQL

UPDATE is the query used to edit existing records in the table. You can use the WHERE clause with the UPDATE statement to update selected rows, if not all rows in the table are affected.

UPDATE command syntax in SQL

 UPDATE TEN_BANG 
 SET cot1 = gtri1, cot2 = gtri2., cotN = gtriN 
 WHERE [DIEU_KIEN]; 

If you want to use more than one condition in WHERE, don't forget the AND and OR operators we already know about in the previous SQL.

Example of UPDATE command in SQL

In this example, we will use the UPDATE command to update the DIACHI field for staff with ID of 3, from Hanam to Hanoi.

UPDATE command in SQL Picture 1

+----+----------+-----+-----------+---------+ | ID | TEN | TUOI| DIACHI | LUONG | +----+----------+-----+-----------+---------+ | 1 | Thanh | 24 | Haiphong | 2000.00 | | 2 | Loan | 26 | Hanoi | 1500.00 | | 3 | Nga | 24 | Hanam | 2000.00 | | 4 | Mạnh | 29 | Hue | 6500.00 | | 5 | Huy | 28 | Hatinh | 8500.00 | | 6 | Cao | 23 | HCM | 4500.00 | | 7 | Lam | 29 | Hanoi | 15000.00| +----+----------+-----+-----------+---------+ 

The statement will look like this:

 SQL > UPDATE NHANVIEN SET DIACHI = 'Hanoi' WHERE ID = 3 ; 

The original NHANVIEN table will become as follows:

UPDATE command in SQL Picture 2

+----+----------+-----+-----------+---------+ | ID | TEN | TUOI| DIACHI | LUONG | +----+----------+-----+-----------+---------+ | 1 | Thanh | 24 | Haiphong | 2000.00 | | 2 | Loan | 26 | Hanoi | 1500.00 | | 3 | Nga | 24 | Hanoi | 2000.00 | | 4 | Mạnh | 29 | Hue | 6500.00 | | 5 | Huy | 28 | Hatinh | 8500.00 | | 6 | Cao | 23 | HCM | 4500.00 | | 7 | Lam | 29 | Hanoi | 15000.00| +----+----------+-----+-----------+---------+

If you want to edit all the values ​​in the DIACHI and LUONG columns in the NHANVIEN table, you do not need to use the WHERE clause, just use the UPDATE command as shown below.

 SQL > UPDATE NHANVIEN SET DIACHI = 'Hanoi' , LUONG = 10000.00 ; 

Now the NHANVIEN board will become:

UPDATE command in SQL Picture 3

+----+----------+-----+-----------+---------+ | ID | TEN | TUOI| DIACHI | LUONG | +----+----------+-----+-----------+---------+ | 1 | Thanh | 24 | Hanoi |10000.00 | | 2 | Loan | 26 | Hanoi |10000.00 | | 3 | Nga | 24 | Hanoi |10000.00 | | 4 | Mạnh | 29 | Hanoi |10000.00 | | 5 | Huy | 28 | Hanoi |10000.00 | | 6 | Cao | 23 | Hanoi |10000.00 | | 7 | Lam | 29 | Hanoi |10000.00 | +----+----------+-----+-----------+---------+

The UPDATE command in SQL is also quite easy to use, right?

In the next section, we will learn about the DELETE query in SQL.

Next lesson: DELETE command in SQL

Previous lesson: Operators associated with AND and OR in SQL