SQL Server doesn’t limit the actions against table expressions (derived tables, common table expres-
about it: a table expression doesn’t really contain data—it’s a reflection of underlying data in base
tables. With this in mind, think of a modification against a table expression as modifying the data in
CHAPTER 8
Data Modification
275
the underlying tables through the table expression. Just as with a SELECT statement against a table
expression, and also with a data modification statement, the definition of the table expression is ex-
panded, so in practice the activity is done against the underlying tables.
Modifying data through table expressions has a few logical restrictions. For example:
■
■
If the query defining the table expression joins tables, you’re only allowed to affect one of the
sides of the join and not both in the same modification statement.
■
■
You cannot update a column that is a result of a calculation; SQL Server doesn’t try to reverse-
engineer the values.
■
■
INSERT statements must specify values for any columns in the underlying
table that do not
have implicit values. A column can get a value implicitly if it allows NULL marks, has a default
value, has an identity property, or is typed as ROWVERSION.
You can find other requirements in SQL Server Books Online, but as you can see, the requirements
make sense.
Now that you know that you can modify data through table expressions, the question is, why
would you want to? One reason is for better debugging and troubleshooting. For example, Listing 8-1
contained the following UPDATE statement.
UPDATE OD
SET discount += 0.05
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1;
Suppose that for troubleshooting purposes, you first want to see which rows would be modified by
this statement without actually modifying them. One option is to revise the code to a SELECT state-
ment, and after troubleshooting the code, change it back to an UPDATE statement. But instead of
making such revisions back and forth between SELECT and UPDATE statements, you can simply use
a table expression. That is, you can define a table expression based on a SELECT statement with the
join query, and issue an UPDATE statement against the table expression. The following example uses
a CTE.
WITH C AS
(
SELECT custid, OD.orderid,
productid, discount, discount + 0.05 AS newdiscount
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1
)
UPDATE C
SET discount = newdiscount;
www.it-ebooks.info
276
Microsoft SQL Server 2012 T-SQL Fundamentals
And here’s an example using a derived table.
UPDATE D
SET discount = newdiscount
FROM ( SELECT custid, OD.orderid,
productid, discount, discount + 0.05 AS newdiscount
FROM dbo.OrderDetails AS OD
JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1 ) AS D;
With the table expression, troubleshooting is simpler because you can always highlight just the
SELECT statement that defines the table expression and run it without making any data changes. With
this example, the use of table expressions is for convenience. However, with some problems, using a
table expression is the only option. To demonstrate such a problem, I’ll use a table called T1 that you
create and populate by running the following code.
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT, col2 INT);
GO
INSERT INTO dbo.T1(col1) VALUES(10),(20),(30);
SELECT * FROM dbo.T1;
The SELECT statement returns the following output showing the current contents of the table T1.
col1 col2
----------- -----------
10 NULL
20 NULL
30 NULL
Suppose that you want to update the table, setting col2 to the result of an expression with the
ROW_NUMBER function. The problem is that the ROW_NUMBER function is not allowed in the SET
clause of an UPDATE statement. Try running the following code.
UPDATE dbo.T1
SET col2 = ROW_NUMBER() OVER(ORDER BY col1);
You get the following error.
Msg 4108, Level 15, State 1, Line 2
Windowed functions can only appear in the SELECT or ORDER BY clauses.
www.it-ebooks.info
