UPDATE command in SQL Server

In SQL Server (Transact-SQL) the UPDATE command is used to update the existing records on a table in a SQL Server database. There are 3 syntaxes for the UPDATE command, depending on whether you are updating the traditional style or updating a table with data from another table.

UPDATE command syntax

Syntax updating tables in SQL Server

 UPD ATE bang 
 SET cot1 = bieuthuc1, 
  cot2 = bieuthuc2, 
  … 
 [WHERE die u_kien]; 

The syntax is to update a table with data from another table in SQL Server, combining the SELECT statement.

 UPDATE board g1 
 SET cot1 = (SELECT bieuthuc1 
  FROM bang2 
  WHERE dieu_kien) 
 [WHERE dieu_k ien]; 

Or another syntax to update a table with data from another table

 UPDATE three ng1 
 SET bang1.cot = bang2.bieuthuc1 
 FROM bang1 
 INNER JOIN bang2 
 ON (bang1.cot1 = bang2.cot1) 
 [WHERE dieu_k ien]; 

Variable name or variable value

cot1, cot2

Column to update.

bieuthuc1, bieuthuc2

New value should be specified for cot1, cot2. Cot1 will assign the value of bieuthuc1, cot2 assigns the value of bieuthuc2 .

WHERE dieu_kien

Option. Conditions must be met in order for the record to be updated.

For example - update 1 column

 UPDATE nhanvien 
 SET ho = 'Johnson' 
 WHERE nhanv = 10; 

This UPDATE command will update the table employee's last name to Johnson if it is 10.

For example - update multiple columns

This is an example of updating more than one column with only one UPDATE command.

 UPDATE  nhanvien 
 SET ten = 'Kyle', 
  nhanvien_id = 14 
 WHERE ho = 'Johnso n'; 

If you want to update multiple columns, simply separate columns / values ​​with commas.

The UPDATE command above will update the name Kyle and nhanvien_id to 14 if the person's last name is Johnson.

For example - update the table with data from another table

This is an example of updating the table with data from another table in MySQL.

 UPDATE staff 
 SET ten = (SELECT ten 
  FROM danhba 
  WHERE danhba.ho = nhanvien.ho) 
 WHERE nhanvien_ id> 95; 

The above example will update all the records in the table if nhanvien_id is greater than 95. If the employee's last name is in the table and the name is the same, the name in the list will be copied to the name in the table.

This UPDATE command can be rewritten with the second syntax below.

 UPDATE anvien 
 SET nhanvien.ten = danhba.ten 
 FROM nhanvien 
 INNER JOIN danhba 
 ON (nhanvien.ho = danhba.ho) 
 WHERE nhanvien _id> 95; 

Previous article: INSERT command in SQL Server

The following article: DELETE command in SQL Server