UPDATE statement in SQL What is that? Use UPDATE SQL how? Let’s find out with Quantrimang.com!
In an era where information technology is increasingly necessary for life, the need to learn programming is also constantly increasing and becoming the hottest industry today. Programming requires highly logical thinking and serious study and research because it is truly a difficult subject. However, if you are truly passionate, there is nothing you cannot learn. Once you have programming knowledge, you can easily create your favorite applications, programs or software. Isn’t it amazing?
There are many programming languages for you to choose from. You can freely choose a favorite language for the goal you are aiming for. However, no matter which language you choose to learn, you should know SQL – Structured Query Language. It serves to store and process information. It integrates effectively with many different programming languages.
In fact, using SQL is not too difficult. Let’s start with the most basic functions and commands. In this article, let’s learn about it update table in SQL okay!
Update command in SQL
Basically, the UPDATE SQL statement is often used to edit existing records in a table. It is part of the Data Manipulation Language – Data manipulation language. Editing data with the UPDATE command does not affect the table structure.
Since UPDATE only interacts with the data in the table, use it very carefully. If you do not select the row to edit first, the data in the table may be affected. You may need to re-insert it or lose important data.
Therefore, to filter the records that need to be modified, you can use the WHERE clause. Using WHERE, you can update one row or multiple rows.
The Update command in SQL uses keys on each row and, at the same time, edits them in a table. When the row is edited, the lock is deployed. Therefore, it is possible to make changes to one row or multiple rows with one query.
UPDATE command syntax in SQL
UPDATE TEN_BANG
SET cot1 = gia_tri1, cot2 = gia_tri2...., cotN = gia_triN
WHERE [DIEU_KIEN];
If you want to use more than one condition in WHERE, don’t forget the AND and OR operators that we learned about in the previous SQL lesson.
Note: Be careful when updating records in a table! Pay attention to the clause WHERE
in command UPDATE
. Clause WHERE
Specifies which record(s) need to be updated. If you omit the clause WHERE
all records in the table will be updated!
Example of UPDATE command in SQL
Update 1 record with specific conditions
In this example, we will use the UPDATE command to update the DIACHI field for employee whose ID is 3, from Hanam to Hanoi.
+----+------+-----+--------+--------+
|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 command will be as follows:
UPDATE NHANVIEN
SET DIACHI = 'Hanoi'
WHERE ID = 3;
The original NHANVIEN table will become as follows:
+----+------+-----+--------+--------+
|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|
+----+------+-----+--------+--------+
Update multiple records
In the employee table example above, if you now want to change people in Hanoi with a LUONG level lower than 2000 to be raised to 3000, the specific command will be as follows:
UPDATE NHANVIEN
SET LUONG = 3000
WHERE DIACHI = "Hanoi' AND LUONG
As a result, after running the above command, you will have the following data table results:
+----+------+-----+--------+--------+
|ID |TEN |TUOI |DIACHI |LUONG |
+----+------+-----+--------+--------+
|1 |Thanh |24 |Haiphong| 2000.00|
|2 |Loan |26 |Hanoi | 3000.00|
|3 |Nga |24 |Hanoi | 3000.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|
+----+------+-----+--------+--------+
Update all records
If you want to edit all values In the DIACHI and LUONG columns in the NHANVIEN table, there is no need to use the WHERE clause, just using the UPDATE command as below is enough:
UPDATE NHANVIEN
SET DIACHI = 'Hanoi', LUONG = 10000.00;
Now the NHANVIEN table will become:
+----+------+-----+--------+--------+
|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 quite easy to use, right? In the next section, we will learn about DELETE queries in SQL.