delete left join postgres

Question

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

Temp data

user330315 · Accepted Answer · 2020-08-20 13:33:17Z

4

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) ;

answered Aug 20, 2020 at 13:33

user330315

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

1 Comment

rkraft Over a year ago

thanks! one follow-up question: assuming in the temp data would only be data for scenario = 2 available and I only want to delete the rows in old data from scenario=2 which are not present anymore in temp data while scenario = 1 is present in old data (i.e. keep rows of scenario = 1 in old data). do you also know how to do that?

Collectives™ on Stack Overflow

delete left join postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related