UPDATE Statements

This YouTube video was created by Steve Griffith.

The UPDATE statement is use to modify or update the existing records in a table. The UPDATE statement is followed by the table name. The SET clause is used to update the value of a specified column. The WHERE clause is use to specify which records should be updated.

WARNING

If the WHERE clause is omitted, ALL records will be updated. There is no UNDO command on a database.

# Update the `director` for the movie with 
# the `movie_title` 'Transcendence' and the `year` 2014
UPDATE `movies` 
SET `director` = 'Wally Pfister'
WHERE `movie_title` = 'Transcendence' and `year` = 2014;

Try it yourself on DB Fiddle

To update more than one column of a record, a comma should be used between each column / value pair. Subqueries can also be used with the UPDATE statement.

# Update the `director` and `genre_id` for the movie with
# the `movie_title` 'Contact' and the `year` 1997
UPDATE `movies` 
SET `director` = 'Robert Zemekis',
  `genre_id` = 
    (SELECT `genre_id` FROM `genres` WHERE `genre_title` = 'Action' LIMIT 1)
WHERE `movie_title` = 'Contact' AND year = 1997;

Try it yourself on DB Fiddle