Delete entries from multiple tables in PostgreSQL-CodePudding

I have 3 tables in my postgreSQL

item_table:

id  item_id
------------
1   1234
2   5678

account_table:

item_id     account_id
----------------------
2       abcd

payment_table:

account_id  payment_status
--------------------------
abcd        good

Description

item_id in the account_table is the id of item_table
account_id of the payment_table is the account_id of account_table

I want to delete the entry from all tables associated with item_id from the item_table

Example : If my item_id is 5678 then I want to delete entries from all tables in which the ids are connected to each other.

How to achieve this in PostgreSQL?

CodePudding user response：

If account.item_id is declared as a foreign key to item.id, you might declare that foreign key as 'ON DELETE CASCADE'. Then deleting a row from 'item' would cascade the deletion 'account'.

Repeat as needed for other tables.

It could be I've misunderstood you, but it's easy enough for you to test.

CodePudding user response：

DELETE p,a FROM 'payment_table' p
LEFTJOIN 'account_table' a ON (p.account_id = a.account_id)
WHERE a.item_id = ?;

Something like this might get you in the right direction.