The SQL UPDATE statement changes data values in a database.
UPDATE can update one or more records in a table.
Use the WHERE clause to UPDATE only specific records.
Change the phone number for supplier Tokio Traders.
UPDATE Supplier
SET Phone = '(03) 8888-5011'
WHERE CompanyName = 'Tokyo Traders'
Be sure to include a WHERE clause or else all records are updated!
UPDATE syntax.
UPDATE table-name
SET column-name1 = value1,
column-name2 = value2, ...
UPDATE syntax with a WHERE clause.
UPDATE table-name
SET column-name1 = value1,
column-name2 = value2, ...
WHERE condition
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
UPDATE Product
SET IsDiscontinued = 1
A BIT value of 1 denotes true.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
UPDATE Product
SET IsDiscontinued = 1
WHERE UnitPrice > 50
A BIT value of 1 denotes true.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
UPDATE Product
SET IsDiscontinued = 1
WHERE Id = 46
This is a common scenario in which a single record is updated.
| SUPPLIER |
|---|
| Id |
| CompanyName |
| ContactName |
| City |
| Country |
| Phone |
| Fax |
UPDATE Supplier
SET City = 'Oslo',
Phone = '(0)1-953530',
Fax = '(0)1-953555'
WHERE Id = 15
This is a common scenario in which a single record is updated.
One way to update multiple rows is to write multiple UPDATE statements.
They can be separated with a semicolon (;) and submitted as a group (called a batch).
Alternatively, use an UPDATE with a WHERE clause. Here is the syntax.
UPDATE table-name SET column1 = value1, ... WHERE condition
An example condition would be: WHERE Id IN (53,24,95,106).
This would update 4 rows with a single UPDATE statement.
Yes, you can UPDATE a NULL value.
Of course, the new value must match the data type of the column.
In this example, all NULL values are replaced with 0.
UPDATE Supplier SET AmountSold = 0 WHERE AmountSold IS NULL
No, only 1 table can be updated with an UPDATE statement.
However, you can use a transaction to ensure that 2 or more UPDATE statements are processed as a single unit-of-work.
Or, you can batch multiple UPDATE statements and submit these as a group.
It depends on the environment in which the UPDATE is executed:
In SMSS, a red error message is returned when an UPDATE fails.
In a programming language, an exception is thrown when an error occurs.
In T-SQL, a TRY/CATCH construct can capture any errors.
Also in T-SQL, the global @ROWCOUNT function returns the number of rows affected by the last UPDATE operation. This can be helpful as well.
