USING Common Table Expression and perform multiple update commands

Question

is it possible to us a CTE to perform multiple update commands?

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)
UPDATE
    Query.FOO = 1;
UPDATE
    Query.BAR = 2;

In the example Query isn't available anymore on the second UPDATE command.

EDIT:

My working code looks like this:

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)
UPDATE
    Query.FOO = 1

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)
UPDATE
    Query.BAR = 2;

Because you can't Update two Tables with one UPDATE command I need two Update commands. The problem right know is, that if I need to change the Select in the CTE I have to do it on two locations in the code.

the regular update syntax is "UPDATE table set col1 = 1, col2 = 2" why not set both columns in the same statement? also i've never used a cte with an update so not sure if it will work. — Jeremy, Mar 22 '16 at 11:44
This isn't specific to SQL Server. The UPDATE command in SQL allows updating of many columns at the same time. The tricky part here though is that this code is trying to update *two tables*. — Panagiotis Kanavos, Mar 22 '16 at 11:45
Can you explain what you actually trying to do? It looks like you're just updating every record in two tables to the same value. — Nick.Mc, Mar 22 '16 at 11:46
That's not a "temporary view". That's a [Common Table Expression](https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx). Precision is an important part of programming and using the correct terms can only help when trying to communicate about programming. — Damien_The_Unbeliever, Mar 22 '16 at 11:50
You can insert your CTE result to a @Table variable and use this Table wherever required in the code block. (You can join this Table with actual table to perform the updates). You can't use the same CTE in multiple statement, bcz CTE is part of the subsequent statement only. — Abdul Rasheed, Mar 22 '16 at 12:13
Why not inserting your records into a temp table, and then do the updates based on that? — , Dec 23 '19 at 11:28

score 6 · Accepted Answer · answered Mar 22 '16 at 11:55

A SQL Server UPDATE only allows you to update a single table. As buried in the documentation:

The following example updates rows in a table by specifying a view as the target object. The view definition references multiple tables, however, the UPDATE statement succeeds because it references columns from only one of the underlying tables. The UPDATE statement would fail if columns from both tables were specified.

Although views and CTEs are not exactly the same thing, they often follow similar rules. So, this is also explained in the section on updatable views:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

You can effectively do what you want by issuing two updates and wrapping them in a single transaction.

I know that I can't UPDATE two tables in one Update command. That's the problem I have. Boiled down I just want to safe some code. In the CTE (Thanks Damien for the explanation) I have a SELECT that's 200 rows code. At the moment I have two CTE's with a Select and Update each. (Which is working) But if I need to change the SELECT I have to do it on two locations in code... — kami, Mar 22 '16 at 12:03

score 6 · Answer 2 · edited Nov 29 '19 at 19:43

6

You can insert your CTE result to a @Table variable and use this Table wherever required in the code block. (You can join this Table with actual table to perform the UPDATE/INSERT/DELETE etc). You can't use the same CTE in multiple statement, because CTE is part of the subsequent statement only.

edited Nov 29 '19 at 19:43

Muhammad Waheed

1,048
1
13
30

answered Mar 22 '16 at 12:18

Abdul Rasheed

6,486
4
32
48

Can someone give me an example how to join a @Table variable to the actual tables? – kami Mar 22 '16 at 12:27
This may be help you http://stackoverflow.com/questions/2189551/sql-server-table-variable-used-in-a-inner-join – Abdul Rasheed Mar 22 '16 at 12:32

Manasse · Answer 3 · 2023-03-30T17:42:07.520

0

You May want to use a temporary table to save the result of your CTE and then update

IF OBJECT_ID(N'tempdb..#temp_cte') IS NOT NULL
BEGIN
  DROP TABLE #temp_cte
END

With Query AS
(
    SELECT
        Table_One.FOO AS FOO,
        Table_Two.BAR AS BAR
    FROM FOO
    JOIN BAR ON FOO.ID = BAR.ID
)

SELECT *
INTO #temp_cte
FROM Query


UPDATE
    Query.FOO = 1


UPDATE
    Query.BAR = 2;

edited Mar 30 '23 at 17:42

answered Mar 30 '23 at 17:22

Manasse

39
7

USING Common Table Expression and perform multiple update commands

3 Answers3