CODEWITHSUNDEEP
postgresql
Derrick Tay
Derrick Tay

Reputation: 73

Deleting data from two tables in one query in postgresql

I got two tables which works the same way as a customer and the transactions that it made. So one customer can have more than one transactions. And so when I delete the customer, I want to delete all the transactions that he has made. Below is my 'customer' table where its has a uuid as the PRIMARY KEY

customer

Below is the 'transaction' table where the column bird_id is refered to the uuid column of the above table.

transaction

I have tried this.

WITH delete_bird_and_entry AS (
   select birdsdata.id from birdsdata
   LEFT JOIN 
   entries ON birdsdata.name = entries.bird_name and birdsdata.species = entries.species_name 
   where birdsdata.id = '6ca574a7-b515-4629-8aa6-4149fcef2bd8'
)

DELETE from birdsdata, entries where id IN (select id from delete_bird_and_entry);

But its not working as it gives me a syntax error.

ERROR:  syntax error at or near ","
LINE 8: DELETE from birdsdata, entries where id IN (select id from d...

Hope my question was clear. This is my first time dealing with postresql so not sure as to how to ask the question. Thank you in advance!

Upvotes: 0

Views: 2574

Answers (2)

user330315
user330315

Reputation:

A single DELETE statement can only delete from a single table. The cascading foreign key is one way to do it.

Another way is to chain the two delete statements using a data modifying CTE

with deleted_birds as (
  delete from birdsdata
  where birdsdata.id = '6ca574a7-b515-4629-8aa6-4149fcef2bd8'
  returning id
)
delete from entries
where bird_id in (select id from deleted_birds);

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246513

Create a cascading foreign key constraint:

ALTER TABLE entries
   ADD FOREIGN KEY (bird_id) REFERENCES birdsdata(id) ON DELETE CASCADE;

Then delete the row in birdsdata, and the dependent rows will be deleted automatically.

Upvotes: 3

Related Questions