This page has 0 threads | Add post

Update queries using CTEs

Written by Andy Brown

In this tutorial

Our example problem
Avoiding Aggregation
Creating the CTE

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).

Our example problem

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.

Avoiding Aggregation

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.

Creating the CTE

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!

Previous
tutorial

Subqueries

Previous
part

Combining more than one CTE in the same query

Top of page

Next
part

Recursive CTEs

Next
tutorial

Programming in SQL

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

CTEs and derived tables

Update queries using CTEs

Our example problem

Avoiding Aggregation

Creating the CTE

Head office

London

Manchester