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.