SQL Server: Using Table Aliases In Delete And Update Queries

A couple of posts ago I said that I would blog about counting unique and distinct values using VBA. I promise I will get to that but today I want to talk about the syntax for using tables aliases in UPDATE and DELETE statements in SQL Server because it’s something I use all the time and I think is a fantastic tip.

First of all, let’s set the scene. Here I’ve created a temp table called #Customers containing four rows:

IF OBJECT_ID('tempdb..#Customers') IS NOT NULL
	DROP TABLE #Customers

CREATE TABLE #Customers
(
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT INTO #Customers
VALUES ('Colin','Legg')
, ('John', 'Smith')
, ('John', 'Legg')
, ('Colin', 'Smith')

SELECT *
FROM #Customers

When running the code you’ll get this output:

(4 row(s) affected)

FirstName            LastName
-------------------- --------------------
Colin                Legg
John                 Smith
John                 Legg
Colin                Smith

(4 row(s) affected)

Suppose we want to delete Colin Legg and John Smith from the table. That’s straightforward enough and can be done as follows:

DELETE
FROM #Customers
WHERE (FirstName = 'Colin' AND LastName = 'Legg')
OR (FirstName = 'John' AND LastName = 'Smith')

SELECT *
FROM #Customers

When we run that we get two rows left which contain John Legg and Colin Smith:

(2 row(s) affected)
FirstName            LastName
-------------------- --------------------
John                 Legg
Colin                Smith

(2 row(s) affected)

So far so good, but it gets more complicated when you’re sourcing the customers you want to delete using information from another table. Let’s start again with this set-up:

IF OBJECT_ID('tempdb..#Customers') IS NOT NULL
	DROP TABLE #Customers

CREATE TABLE #Customers
(
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

IF OBJECT_ID('tempdb..#CustomersToDelete') IS NOT NULL
	DROP TABLE #CustomersToDelete

CREATE TABLE #CustomersToDelete
(
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT INTO #Customers
VALUES ('Colin','Legg')
, ('John', 'Smith')
, ('John', 'Legg')
, ('Colin', 'Smith')

INSERT INTO #CustomersToDelete
VALUES ('Colin','Legg')
, ('John', 'Smith')
, ('Robert', 'Jones')

SELECT *
FROM #Customers

SELECT *
FROM #CustomersToDelete

This time we have two temp tables and running the above code gives this output:

(4 row(s) affected)

(3 row(s) affected)

FirstName            LastName
-------------------- --------------------
Colin                Legg
John                 Smith
John                 Legg
Colin                Smith
(4 row(s) affected)

FirstName            LastName
-------------------- --------------------
Colin                Legg
John                 Smith
Robert               Jones
(3 row(s) affected)

Let’s say we want to delete the customers in the #Customers table if they’re listed in the #CustomersToDelete table. The first thing I do in these situations is write a SELECT query which will show me the rows I’m going to end up deleting. There are a few different ways this can be done (such as using INTERSECT or an INNER JOIN)  but here I’m going to use a correlated subquery:

SELECT *
FROM #Customers c
WHERE EXISTS (
	SELECT NULL
	FROM #CustomersToDelete ctd
	WHERE c.FirstName = ctd.FirstName
	AND c.LastName = ctd.LastName)

Correlated subqueries aren’t anything to be afraid of. The subquery is the query nested inside the outer query:

SELECT *
FROM #Customers c
WHERE EXISTS (
	SELECT NULL	
	FROM #CustomersToDelete ctd
	WHERE c.FirstName = ctd.FirstName 
	AND c.LastName = ctd.LastName)

And it’s correlated because the subquery and the outer query are talking to one another. You’ll notice that the ‘c’ table alias in the outer query is also used in the subquery.

This query returns 2 rows because Robert Jones doesn’t exist in the #Customers temp table:

FirstName            LastName
-------------------- --------------------
Colin                Legg
John                 Smith

(2 row(s) affected)

Now that we’re happy that the SELECT query gives the rows we want to delete, we just have to convert it into a DELETE query. We can start by replacing the SELECT * with a DELETE:

DELETE
FROM #Customers c
WHERE EXISTS (
	SELECT NULL
	FROM #CustomersToDelete ctd
	WHERE c.FirstName = ctd.FirstName
	AND c.LastName = ctd.LastName)

Unfortunately that query isn’t going to run. The syntax is invalid and SSMS will start underlining words with red squiggly lines because it’s not happy: it’s the ‘c’ table alias that’s causing the problem. The next step is to look at the DELETE (T-SQL) syntax in the helpfile. It all looks quite confusing but, using the information in the arguments section, we can whittle down what we’re interested in to this:

DELETE 
    [ FROM ] 
    { table_alias }
    [ FROM table_source [ ,...n ] ] 
    [ WHERE {   } ]

And using that as our guide, we can update the query to this slightly unusual-looking beast:

DELETE
FROM c
FROM #Customers c
WHERE EXISTS (
	SELECT NULL
	FROM #CustomersToDelete ctd
	WHERE c.FirstName = ctd.FirstName
	AND c.LastName = ctd.LastName)

The notes in the arguments section of the helpfile explain that the first FROM is an optional keyword. Since there’s no recommendation on whether it should be left in or not, we can quite happily remove it from the query:

DELETE c
FROM #Customers c
WHERE EXISTS (
	SELECT NULL
	FROM #CustomersToDelete ctd
	WHERE c.FirstName = ctd.FirstName
	AND c.LastName = ctd.LastName)

When we run either of these we versions get a confirmation in the Results window that 2 rows were deleted and then if we check what’s left in the customers table we’ll see that John Legg and Colin Smith are still in there.

I use this type of query on a daily basis – you’ll be amazed at how often you’ll want to use table aliases for query readability so I can’t emphasise enough how useful it is. There are even situations when you’re forced to use table aliases, such as when you want to join a table onto itself (a self join) or when you have a correlated subquery where the same table is used in both the outer query and the subquery. Examples of the both of these situations can be found on MSDN here.

Moving on, it’s a very similar story if you want to do an UPDATE query with table aliases.

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
	DROP TABLE #Orders

IF OBJECT_ID('tempdb..#OrdersToUpdate') IS NOT NULL
DROP TABLE #OrdersToUpdate

CREATE TABLE #Orders
(
OrderNumber SMALLINT IDENTITY (1,1),
OrderPriority VARCHAR(6)
)

CREATE TABLE #OrdersToUpdate
(
OrderNumber SMALLINT
)

INSERT INTO #Orders
VALUES ('Low'),('Low'),('Low'),('Low')

INSERT INTO #OrdersToUpdate
VALUES (1),(4)

SELECT *
FROM #Orders

SELECT *
FROM #OrdersToUpdate

Running the above code gives us this output:

(4 row(s) affected)

(2 row(s) affected)
OrderNumber OrderPriority
----------- -------------
1           Low
2           Low
3           Low
4           Low

(4 row(s) affected)

OrderNumber
-----------
1
4

(2 row(s) affected)

In this case we want to change the priority of orders in the #Orders table to ‘High’ if they exist in the #OrdersToUpdate table. Again, we can start by writing a SELECT query to identify the orders we’ll be updating:

SELECT *
FROM #Orders o
WHERE EXISTS (
	SELECT NULL
	FROM #OrdersToUpdate otu
	WHERE o.OrderNumber = otu.OrderNumber)

Which gives:

OrderNumber OrderPriority
----------- -------------
1           Low
4           Low

(2 row(s) affected)

Converting that into an UPDATE query gives us this:

UPDATE o
SET o.OrderPriority = 'High'
FROM #Orders o
WHERE EXISTS (
	SELECT NULL
	FROM #OrdersToUpdate otu
	WHERE o.OrderNumber = otu.OrderNumber)

When we run that code the Results window shows that 2 rows were affected and then we can see what we’ve got in the #Orders table:

SELECT *
FROM #Orders

Giving:

OrderNumber OrderPriority
----------- -------------
1           High
2           Low
3           Low
4           High

(4 row(s) affected)
Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft SQL Server and tagged , , , , , . Bookmark the permalink.

One Response to SQL Server: Using Table Aliases In Delete And Update Queries

  1. sam says:

    Below works for me in Access and should work is most other DB’s
    DB_SALES is a Table containing Sales Records
    QDB_SALES_CANCEL is a Query on the above tables that selects ORDER ID’s to be deleted based on certain criteria

    DELETE DB_SALES.ORD_NO, *
    FROM DB_SALES
    WHERE EXISTS (SELECT NULL FROM QDB_SALES_CANCEL
    Where DB_SALES.ORD_NO = QDB_SALES_CANCEL.ORD_NO and DB_SALES.YEAR_FP = QDB_SALES_CANCEL.YEAR_FP and DB_SALES.DEPT=QDB_SALES_CANCEL.TECH_DEPT);

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s