I expect the SQL statement to update a limited amounts of rows, instead of every row:
UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID;
I expect the statement to behave the same as the statements:
UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;
UPDATE Join_Test_1
SET ANumber = 3
WHERE EXISTS (SELECT * FROM Join_Test_2
WHERE Join_Test_2.PKID = Join_Test_1.PKID);
UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);
The statements using the WHERE clause only update the ANumber column where the PKID matches.
The statement using the INNER JOIN updates all rows in the table.
Why does the INNER JOIN not limit the number of rows updated?
Can the statement with the INNER JOIN be rewritten to use the JOIN to limit the number of rows updated?
/* Expansive Example */
CREATE TABLE Join_Test_1 (PKID SERIAL, ANumber INTEGER);
CREATE TABLE Join_Test_2 (PKID SERIAL, ANumber INTEGER);
INSERT INTO Join_Test_1 (ANumber) VALUES (1), (1);
INSERT INTO Join_Test_2 (ANumber) VALUES (2);
UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID; -- Updates 2
SELECT *
FROM Join_Test_1
ORDER BY PKID;
-- 1, 3
-- 2, 3
UPDATE Join_Test_1
SET ANumber = 1; -- Update 2
UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;
SELECT *
FROM Join_Test_1
ORDER BY PKID;
-- 1, 1
-- 2, 3
UPDATE Join_Test_1 SET ANumber = 1; -- Update 2
UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);
SELECT *
FROM Join_Test_1
ORDER BY PKID;
-- 1, 1
-- 2, 3
DROP TABLE IF EXISTS Join_Test_1;
DROP TABLE IF EXISTS Join_Test_2;
In plpgSQL there is an way to update a single table during joing as follows:
CREATE TABLE Join_Test_1 (PKID SERIAL,ANumber INTEGER);
CREATE TABLE Join_Test_2 (PKID SERIAL,ANumber INTEGER);
INSERT INTO Join_Test_1 (ANumber) VALUES (1),(1);
INSERT INTO Join_Test_2 (ANumber) VALUES (2);
select * from Join_Test_1;
-- 1 1
-- 2 1
select * from Join_Test_2;
-- 1 2
WITH TT AS (
UPDATE Join_Test_1
SET ANumber = 3
RETURNING *
)
SELECT *
FROM TT AS ST1
INNER JOIN
Join_Test_2 AS ST2
ON ST1.PKID = ST2.PKID;
-- 1 3 1 2
SELECT * FROM Join_Test_1 ORDER BY PKID;
-- 1 3
-- 2 3