564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our complete SQL tutorial. You can learn more about these techniques on our Advanced SQL training course. |
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 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
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
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'
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.