PostgreSQL SELECT query using same structure as a DELETE query

Question

With MySQL, I perform a delete with a join as follows:

DELETE t1
FROM t1
LEFT OUTER JOIN t2 ON t2.fk_id = t1.id
WHERE t2.id IS NULL;

Before executing the query, I would typical make sure I am okay with the outcome first by viewing the soon to be deleted records as follows:

SELECT t1.*
FROM t1
LEFT OUTER JOIN t2 ON t2.fk_id=t1.id
WHERE t2.id IS NULL;

Using PostgreSQL, I believe I should perform the same delete action as follows (EDIT below):

DELETE FROM t1
USING t2
WHERE t1.id = t2.fk_id
AND t2.id IS NULL;

Concerned that I might not be correct, I unsuccessfully tried the following:

SELECT t1.*
USING t2
WHERE t2.fk_id=t1.id
AND t2.id IS NULL;

Is there a way with PostgreSQL to perform a SELECT query using the same structure as a DELETE query?

EDIT - Not even correct since I am effectively performing an INNER JOIN and will not delete any records, and will need to change it as follows. Still would appreciate an answer to my original question.

DELETE FROM t1  
USING t1 t_1
LEFT OUTER JOIN t2 ON t2.fk_id=t_1.id
WHERE t1.id=t_1.id AND t2.id IS NULL;

user330315 · Accepted Answer · 2020-09-12 13:45:52Z

3

I would use a NOT EXISTS condition:

delete from t1
where not exists (select *
                  from t2
                  where t1.id = t2.fk_id);

That's typically faster than a join and it's also standard compliant SQL.

answered Sep 12, 2020 at 13:45

user330315

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

1 Comment

user1032531 Over a year ago

Faster, standard compliant SQL, AND it is easy to test results before deleting. Works for me! Thanks

Collectives™ on Stack Overflow

PostgreSQL SELECT query using same structure as a DELETE query

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