Deleting data from two tables in one query in postgresql

Question

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

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

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!

You're error says there's a syntax error near a comma but I don't see a comma in your code at all, and I also second setting up a cascading delete constraint to make deletes like this easier — Matthew Barlowe
– Matthew Barlowe, Commented Jun 5, 2021 at 4:31
sorry i have edited my post. I think i accidentally delete some part — Derrick Tay
– Derrick Tay, Commented Jun 5, 2021 at 4:35

Laurenz Albe · Accepted Answer · 2021-06-05 04:32:00Z

3

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.

answered Jun 5, 2021 at 4:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

user330315 · Accepted Answer · 2021-06-05 08:21:45Z

2

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

answered Jun 5, 2021 at 8:21

user330315

Collectives™ on Stack Overflow

Deleting data from two tables in one query in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related