Update query with null left join condition in postgreSQL

Question

I'm currently migrating from SQL Server to PostgreSQL and got confuse with update query in postgres.

I have query like this in SQL Server:

UPDATE t1
SET col1 = 'xx'
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id is null

how do you do this in postgres?

thanks in advance

user330315 · Accepted Answer · 2021-10-15 06:52:46Z

4

The left join is used to simulate a "NOT EXISTS" condition, so you can rewrite it to:

update table1 t1
   set col1 = 'xx'
where not exists (select *
                  from table2 t2
                  where t1.id = t2.id);

As a side note: Postgres works differently than SQL Server and in general you should not repeat the target table of an UPDATE statement in the FROM clause in Postgres.

answered Oct 15, 2021 at 6:52

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update query with null left join condition in postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related