SQL UPDATE

Introduction

Syntax

UPDATE Syntax

The UPDATE statement modifies tuples of a table. Each named attribute is set to the value of the expression on the right hand side of the equal sign. If no WHERE clause is specified, every tuple is updated. (Dangerous!) If a WHERE clause is specified, then only those tuples which satisfy the search condition are updated. 

        
UPDATE table-list
...    SET column-name = expression, ...
...    [WHERE search-condition]

Examples

Update a member Address - Incorrectly
          
UPDATE member
  SET memberAddress = '4 Brown Street, Leamington, ON'
  WHERE memberSurname = 'Brown'

Updates the address field for all members with the last name 'Brown'. This is marked as 'prohibited' since if the intent is to update the address for a single member, this works only if there is only one member with the surname 'Brown'. Otherwise all members named 'Brown' end up on Brown street.

Update a member Address - Correctly
          
UPDATE member
  SET address = '4 Brown Street, Leamington, ON'
  WHERE memberId = 5

Updates the address field only for a single member since the WHERE clause refers to a primary key field. This is the required approach if you intend to update a single tuple.

Update Multiple Tuples
          
UPDATE book
  SET price = 1.5 * price
  WHERE publisher = 'Penguin'

Increases the price of all books published by Penguin in the book table by 50%. Attribute values can be used in their own UPDATE statements. This is a case where not using the primary key in a WHERE clause is acceptable since the intent is that all tuples from a particular publisher are to be updated by the same amount.

Update Multiple Tuples
          
UPDATE borrowed
  SET dateReturned = CURRENT_DATE()
  WHERE dateReturned IS NULL
  AND isbn = '978-0921821236'

Updates borrowed to show that the outstanding copy (assuming there is only one) of the book with isbn '978-0921821236' has been returned today. Note that the NULL is compared using IS rather than '='.

CURRENT_DATE() is the MySQL dialect function that returns the current date in "YYYY-MM-DD" format. CURDATE() is equivalent. NOW() returns the current date and time in "YYYY-MM-DD HH:MM:SS" format.