sqlpostgresqlsql-update

Update Table A if no referencing row in table B exists, limit updates

The PK of table1 is a FK in table2.

I wish to update the status in table1 where no record exists in table2 and limit the number of updates. There may be no records in table2.

Something like:

UPDATE t1
SET status = 0
WHERE NOT EXISTS (
    SELECT id
    FROM t2
    WHERE t1.id = t2.id
    LIMIT 1000
)
Solution

  • This is a little complicated in Postgres, because there is no limit. Assuming that you have a primary key in t1 (which I'll assume is id), you can use a subquery to determine the rows to update and then match in the WHERE clause:

    UPDATE t1
    SET status = 0
    FROM (SELECT tt1.*
          FROM t1 tt1
          WHERE NOT EXISTS (SELECT t2.id FROM t2 WHERE tt1.id = t2.id)
          LIMIT 1000
         ) ttl
    WHERE t1.id = tt1.id;