Question 415845 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – UPDATE Statement ignores INNER JOIN

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);
SELECT * FROM Join_Test_1 ORDER BY PKID;

The statements using the WHERE-Clause only update the "ANumber" field where the PKID matches.
The statement using the INNER JOIN updates all fieds 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   EXISTS (SELECT * FROM Join_Test_2 WHERE Join_Test_2.PKID = Join_Test_1.PKID);
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;
Tags:

3

Answers


  1. 0

    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

    Process-01: db<>fiddle – Update During joining

    Process-02: db<>fiddle – Update During joining

    Process-03: db<>fiddle – Normal way

    Login or Signup to reply.
  2. 0

    You alias the table Join_Test_1, but you don’t call the alias in the update. That means the FROM statement is ignored. You statement is the same as UPDATE Join_Test_1 SET ANumber = 3.

    UPDATE  ST1
SET     ANumber = 3
FROM    Join_Test_1 AS ST1
INNER JOIN  Join_Test_2 AS ST2
ON          ST1.PKID = ST2.PKID;
    Login or Signup to reply.
  3. 0

    Arguably, when dealing with updates that involve multiple or complex joins and/or conditions – the most readable and maintainable way to do it is using the MERGE INTO sintax.

    MERGE INTO Join_Test_1 target
USING ( Select     t1.pkid 
        From       Join_Test_1 t1
        Inner Join Join_Test_2 t2 ON(t2.pkid = t1.pkid)
      ) src ON(src.pkid = target.pkid)
WHEN MATCHED THEN 
    UPDATE SET anumber = 3;

    SELECT * FROM Join_Test_1 ORDER BY PKID;

    R e s u l t :

    pkid anumber
    1 3
    2 1

    fiddle

    Just make sure that your USING query fetches unique rows to be matched…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search