O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

UPDATE

Synopsis

The UPDATE command changes existing data in a table.

Vendor

Command

SQL Server

Supported, with variations

MySQL

Supported, with variations

Oracle

Supported, with variations

PostgreSQL

Supported

SQL99 Syntax and Description

UPDATE {table_name | view_name} 
SET {column_name | variable_name} = {DEFAULT | expression} [,...n]
WHERE conditions

As with the DELETE statement, an UPDATE command is seldom issued without a WHERE clause, since the statement affects every row in the entire table.

Warning

It is good practice to issue a SELECT command using the same WHERE clause before issuing the actual UPDATE statement. This checks all rows in the result set before actually performing the UPDATE. Whatever rows are returned by the SELECT are modified by the UPDATE.

Examples

A basic UPDATE statement without a WHERE clause looks like this:

UPDATE authors
SET contract = 0

Without a WHERE clause, all authors in the authors table have their contract status set to (meaning they don’t have a contract any more). Similarly, values can be adjusted mathematically with an UPDATE statement:

UPDATE titles
SET price = price * 1.1

This UPDATE statement would increase all book prices by 10%.

Adding a WHERE clause to an UPDATE statement allows records in the table to be modified selectively:

UPDATE titles 
SET    type  = 'pers_comp',
       price = (price * 1.15)
WHERE  type  = 'popular_com'

This query makes two changes to any record of the type `popular_com'. The command increases their price by 15% and alters ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required