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