delete left join postgres

user_12850613 0
Viewed 179

Problem: I have the following two tables. I want to delete the rows of old data which are not present in temp data. Primary keys are id and scenario. I ran the following delete left join in postgres which unfortunately did not work out (it deletes the whole table):

DELETE
FROM old_data
USING old_data as a
LEFT OUTER JOIN temp_data b ON (b.id = a.id AND b.scenario=a.scenario) 
      WHERE (b.scenario IS NULL AND a.scenario = 2)

Any ideas how to adjust the query?

## Old data

enter image description here

Temp data

enter image description here

postgresql database
1 Answers

You don't need a left join for that:

DELETE FROM old_data od
where not exists (select * 
                  from temp_data td
                  where td.id = od.id 
                    AND td.scenario = od.scenario) ;
user_3303150
Related