SQL UPDATE

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!

SQL Insert

SQL Delete

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

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

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

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

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

PRODUCT
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

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

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.

#