Deleting and Updating Records in SQL
Part three of a three-part series of blogs

This blog teaches you how to modify existing data in your database by either deleting records or updating them.

  1. Modifying Data in SQL
  2. Deleting Data From a Table
  3. Updating Existing Data (this blog)

This blog is part of our complete SQL tutorial. You can learn more about these techniques on our Advanced SQL training course.

Posted by Andrew Gould on 08 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Updating Existing Data

The UPDATE statement allows you to change the values of existing records in your tables. The basic syntax of the statement is shown below:

UPDATE TableName

SET ColumnName1 = Value

,ColumnName2 = Value

Updating Every Record in a Table

Updating every record in a table is easy enough; you simply specify which columns you want to change and which values you'd like to use. The example below changes the DateLastModified column to today's date and the FilmBudgetDollars column to 110% of its current value:

UPDATE tblFilm

SET DateLastModified = GETDATE()

,FilmBudgetDollars = FilmBudgetDollars * 1.1

Using Criteria When Updating Records

You can add a WHERE clause to your UPDATE statements to limit the rows that are being updated. The example below updates the box office takings of any film that wins at least one Oscar:

UPDATE tblFilm

SET FilmBoxOfficeDollars = FilmBoxOfficeDollars + 1000000

WHERE FilmOscarWins > 0

Referring to Other Tables When Updating Records

You may wish to update the records in one table based on criteria that refer to values in other tables. You can achieve this by adding a FROM clause to your statement. The example below increases the number of Oscar nominations for any film directed by Steven Spielberg:

UPDATE tblFilmBackup

SET FilmOscarNominations = FilmOscarNominations + 1

FROM

tblFilm AS f INNER JOIN

tblDirector AS d ON d.DirectorID = f.FilmDirectorID

WHERE

d.DirectorName = 'Steven Spielberg'

Using Subqueries When Updating Data

You can also use subqueries in your UPDATE statements. The example below populates a column in the studio table by calculating the total budget for each studio from the film table:

UPDATE tblStudio

SET StudioTotalBudget =

(SELECT SUM(CAST(FilmBudgetDollars AS BIGINT))

 FROM tblFilm

 WHERE FilmStudioID = StudioID)

The end result is shown in the diagram below:

Total budget for studio

After running the above query these are the values stored in the studio table.

 
  1. Modifying Data in SQL
  2. Deleting Data From a Table
  3. Updating Existing Data (this blog)
This blog has 0 threads Add post