CODEWITHSUNDEEP
sqlpostgresqlsql-delete
SkinnyBetas
SkinnyBetas

Reputation: 501

Delete rows from multiple tables in a single statement

I've got 2 tables say users and articles, I need to delete a user and all instances of that user from articles as well but I need to do it in a single statement. So I can't say:

DELETE FROM users WHERE id = 1
DELETE FROM articles WHERE user_id = 1

Is there a way I can say something like:

SELECT u.name, u.id, a.user_id
FROM users u, articles a
WHERE u.id = a.user_id
DELETE FROM thistable
WHERE id = 1

Any help would be greatly appreciated

Upvotes: 0

Views: 386

Answers (1)

Jim Jones
Jim Jones

Reputation: 19693

The standard way to do that is to declare the foreign key as ON DELETE CASCADE. Doing so, if a user is deleted, all articles related to it will also be deleted.

CREATE TABLE articles (
  user_id int REFERENCES users (id) ON DELETE CASCADE);

Demo: db<>fiddle

If for some reason you're not able to change the table structure, consider using a DELETE ... RETURNING from users inside a CTE and then use the returned values in a outer DELETE on articles:

WITH j (id) AS (
  DELETE FROM users WHERE id = 1
  RETURNING id
)
DELETE FROM articles a USING j 
WHERE a.user_id = j.id;

Demo: db<>fiddle

Upvotes: 2

Related Questions