Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
542 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andrew Gould
In this tutorial
There are three main things you can do to modify the data stored in your database: insert new records into a table, update existing records, and delete existing records.
We've dealt with inserting records into tables in a previous part of this tutorial. This article is going to concentrate on how you update and delete records.
Before you attempt to delete or update records in SQL you need to be aware that you can't undo your actions. Make sure that when you practise these techniques you have a backup copy of your data (I speak from bitter experience!).
You can provide yourself with a safety net when modifying data by using transactions. A transaction allows you to undo any changes that you have made since it began. We'll cover transactions in a separate blog.
Now that you've been suitably warned we can look at how to delete data from a table using the DELETE statement. Read the next part of this blog to discover how it works.
To delete data from a table you use the DELETE statement. This statement affects entire records, so you can't use this technique to remove a value from an individual column.
The syntax for deleting records from a table is straightforward enough:
DELETE FROM MyTableName
WHERE Condition = True
So, for example, the code below will delete any film whose running time is less than 100 minutes:
DELETE FROM tblFilm
WHERE FilmRunTimeMinutes < 100
When you execute the code you'll be told how many records you have irretrievably removed from the table:
I hope you selected the correct records, because you can't get them back!
You can add multiple criteria to the WHERE clause when you're deleting records. You might want to read our blog on using criteria in queries to give you an idea of the things you can do.
You might find that you need to delete records from one table using criteria that are based on values held in other tables. There are two solutions to this problem. The first technique involves the use of subqueries. The example below will delete any film from the film table that was directed by Steven Spielberg:
DELETE FROM tblFilm
WHERE FilmDirectorID =
(SELECT DirectorID
FROM tblDirector
WHERE DirectorName = 'Steven Spielberg')
The subquery in the above example returns the id number of Steven Spielberg. The WHERE clause compares this result with the value of the FilmDirectorID field to determine whether a row should be deleted.
You can achieve the same result by using a second FROM clause in the DELETE statement. The example below will again delete any films by Steven Spielberg:
DELETE FROM tblFilm
FROM
tblFilm AS f INNER JOIN
tblDirector AS d ON f.FilmDirectorID = d.DirectorID
WHERE
d.DirectorName = 'Steven Spielberg'
The second FROM clause specifies the set of records from which rows can be deleted. The first FROM clause deletes the corresponding rows from the specified table.
There are two techniques you can use to delete every record from a table. The first method involves using the DELETE statement without a WHERE clause, as in the example below:
DELETE FROM tblFilm
It's more efficient however to use the TRUNCATE statement instead. This method is faster, and uses fewer system resources and transaction log resources.
TRUNCATE TABLE tblFilm
We've seen how to remove data from a table but what if you only want to modify existing data? The answer is to use the UPDATE statement and the next part of this series explains how it works.
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.
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.