Can I perform commands on multiple tables with specific values in SQL Server without using identical WHERE clauses each time?

Viewed 26

I have a large number of operations I want to perform on database tables that meet a particular condition for a shared column name, and am wondering if this can be done without repetitive use of WHERE.

Say I've got these two tables and I want to make changes only to rows with General values of 2.

CREATE TABLE table1 (
    General int,
    Specific int)

INSERT INTO table1
VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3)

CREATE TABLE table2 (
    General int,
    Specific int)

INSERT INTO table2
VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3)

Obviously I can do this:

UPDATE table1
SET Specific = Specific + 1
WHERE General = 2

UPDATE table2
SET Specific = Specific - 1
WHERE General = 2

But is there a way I can do this without having "WHERE General = 2" each time? I thought I might be able to do this:

WITH 
table1_CTE
AS
(
    SELECT *
    FROM table1
    WHERE General = 2
),
table2_CTE
AS
(
    SELECT *
    FROM table2
    WHERE General = 2
)

UPDATE table1_CTE
SET Specific = Specific + 1

UPDATE table2_CTE
SET Specific = Specific - 1;

But "WITH" it seems is only for single commands.

0 Answers
Related