Delete with LEFT OUTER JOIN in PostgreSQL

Question

I'm trying to remove all rows from one table that don't have the corresponding key in a second table. So for example in my devices table I have id values of 1 and 2. In my registrations table I have deviceid of 1.

Because 2 doesn't exist in the registration table, I want to remove that row from the devices table, but leave id 1.

In Microsoft SQL I'd write something like this:

DELETE d
FROM devices d
LEFT OUTER JOIN registrations r on d.id = r.deviceid
WHERE r.deviceid IS NULL

What's the equivalent in PostgreSQL? I tried the below, but it deleted all rows from devices:

DELETE FROM devices
USING devices AS d
LEFT OUTER JOIN registrations AS r ON d."id" = r.deviceid
WHERE r.deviceid IS NULL

user330315 · Accepted Answer · 2019-11-22 06:46:07Z

5

I would use a NOT EXISTS condition:

delete from devices d
where not exists (select * 
                  from registrations r
                  where d.id = r.deviceid);

answered Nov 22, 2019 at 6:46

user330315

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

1 Comment

Gargoyle Over a year ago

Thanks. In 8 minutes when StackOverflow decides I've had time to read the answer I'll mark it as correct.

Collectives™ on Stack Overflow

Delete with LEFT OUTER JOIN in PostgreSQL

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