It is a clause in the UPDATE statement which sets a new value for a specific column

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 condition

More Examples

UPDATE

Problem: Discontinue all products in the database.

UPDATE Product
   SET IsDiscontinued = 1

A BIT value of 1 denotes true.

Result:  77 records updated.

UPDATE WHERE

Problem: Discontinue all products over $50.

UPDATE Product
   SET IsDiscontinued = 1
 WHERE UnitPrice > 50

A BIT value of 1 denotes true.

Result:  7 records updated.

UPDATE Single Record

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

Problem: Supplier Norske Meierier (Id = 15) has moved.
Change their city, phone, and fax with updated values.

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.

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 condition

An 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 NULL

Can 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

Which clause is used to update the data in SQL?

Which clause should you include in the update statement to update multiple columns?

What is the function of an AS clause change data type to name a column with alias to apply formatting to SELECT columns?

Which clause is used with the update command to update only selected rows?