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
546 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 Andy Brown
In this tutorial
I'm a big fan of CTEs, although not of the name (what on earth does Common Table Expression mean?), but I find the syntax of update queries almost impossible to remember when they involve more than one table (it's the only thing in SQL that I regularly have to Google).
So imagine that you have a database containing a table of potential pet owners:
You have 4 possible owners in your database.
You also have a table of pets that each owner has:
You can see from this that Boris the owner has 3 pets: a dog, a fish and a rabbit (his owner id number 3 crops up three times in the pets table).
You want to write a query to update the Pets column in the Owner table to hold a comma-delimited list of the pets owned by each person.
If you want to try the code below out yourself, download this file, unzip it and run the script it contains to generate your own simple pets database.
CTEs provide a wonderful way to update tables, but you can't do this when they group data together. So the following query won't work as a way to gather together the data that you want to update:
SELECT
o.OwnerId,
o.OwnerName,
-- get the owner's pets
STRING_AGG(p.PetName,',') AS PetList
FROM
Owner AS o
JOIN Pet AS p ON p.OwnerId = o.OwnerId
GROUP BY
o.OwnerId,
o.OwnerName
Although this gives you the right answer:
This does indeed show for each owner the pets they own.
You would get this error message if you tried to update the Owner table via a CTE based upon this:
A message explaining that you can't update tables through a CTE containing a GROUP BY clause.
The reason is obvious: if a query is based on data from two different tables, how would SQL know which to update?
In our example you can solve this by using a correlated subquery:
SELECT
o.OwnerId,
o.OwnerName,
-- find out the owner's pets
(
SELECT STRING_AGG(p.PetName,',') AS PetList
FROM Pet AS p
WHERE p.OwnerId = o.OwnerId
) AS PetList
FROM
Owner AS o
This gives us the same output as shown above, but based upon a single table.
Before you create the final query, you should add one more column to the above query - the one you want to update:
-- you must include the column you
-- want to update
o.Pets,
You can then write the update query:
-- create a CTE to hold owners and number of pets
WITH OwnerPets AS (
SELECT
o.OwnerId,
o.OwnerName,
-- you must include the column you
-- want to update
o.Pets,
-- find out the owner's pets
(
SELECT STRING_AGG(p.PetName,',') AS PetList
FROM Pet AS p
WHERE p.OwnerId = o.OwnerId
) AS PetList
FROM
Owner AS o
)
-- now update the details for each owner
UPDATE OwnerPets
SET Pets = PetList
The beauty of this is that the syntax of the final update query is so simple, because it's updating through the CTE!
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.