UPDATE 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.
Example
#
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!
Syntax
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 conditionMore Examples
UPDATE
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
Problem: Discontinue all products in the database.
UPDATE Product SET IsDiscontinued = 1A BIT value of 1 denotes true.
Result: 77 records updated.
UPDATE WHERE
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
Problem: Discontinue all products over $50.
UPDATE Product SET IsDiscontinued = 1 WHERE UnitPrice > 50A BIT value of 1 denotes true.
Result: 7 records updated.
UPDATE Single Record
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
Problem: Discontinue the product with Id = 46.
UPDATE Product SET IsDiscontinued = 1 WHERE Id = 46 This is a common scenario in which a single record is updated.
Result: 1 record updated.
UPDATE Multiple Columns
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
Problem: Supplier Norske Meierier (Id = 15) has moved.
Change their city, phone, and fax with updated values.
This is a common scenario in which a single record is updated.
Result: 1 record updated.
Developers also Ask
What are the 3 update commands in SQL?
These are the commands that create, modify or delete data in a database:
- INSERT - adds a single or multiple records in the table
- UPDATE - modifies an existing record
- DELETE - removes a record from the database
How do you write multiple UPDATE statements in SQL?
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 conditionAn example condition would be: WHERE Id IN (53,24,95,106).
This would update 4 rows with a single UPDATE statement.
Can we UPDATE a NULL value in SQL?
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 NULLCan we UPDATE multiple tables with a single SQL query?
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.
How do I know that an UPDATE was successful?
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.
You may also like
- Our Sql INSERT Tutorial
- Our Sql DELETE Tutorial
- Our Sql WHERE Tutorial
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.
- Sql INSERT
- Sql UPDATE
- Sql DELETE
- Sql JOIN
- Sql LEFT JOIN
- Sql RIGHT JOIN
- Sql WHERE
- Sql WHERE LIKE
- Sql WHERE IN
- Sql ORDER BY
- Sql GROUP BY
- Sql Subqueries
- Sql Identity
- Sql Primary Key
- Sql Foreign Key
- Sql Create Table
- Sql Create View
- Sql Create Index
- Sql Functions
- Sql Triggers
- Sql Procedures
- Sql Training
- Sql Developers
- Sql Interviews