BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
This blog teaches you how to modify existing data in your database by either deleting records or updating them.
- Modifying Data in SQL
- Deleting Data From a Table
- 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:
After running the above query these are the values stored in the studio table.
- Modifying Data in SQL
- Deleting Data From a Table
- Updating Existing Data (this blog)