How to delete data from both tables or least one of them, when using “delete from … using … where” in Postgresql?

Posted on

Question :

I have this sql script for my Postgresql db:

  DELETE FROM table1
  USING table2
  WHERE table2.frg_table1_id = table1.id
  AND table1.id = $1

I want to it to always delete data from table1 if the condition table1.id = $1 is met. And, optionally, delete corresponding data from table2 too. Even when corresponding data in table2 doesn’t exist, it still must delete from table1

An issue now is that, it’ll delete data either from both tables or from none.

How should I twist my script?

Answer :

A single DELETE statement only deletes rows from a single table (ignoring table inheritance for now). So if you want to delete from two tables, you have to run two delete statements.

You can do it in “one” statement, but using a common table expression that first deletes from table2, then from table1:

with t2_deleted as (
  delete from table2
  where frg_table1_id = $1
)
delete from table1
where id = $1;

But in the end, this is exactly the same as simply running two DELETE statements in a single transaction:

delete from table2
where frg_table1_id = $1;

delete from table1
where id = $1;

Like @a_horse_with_no_name said you need to execute 2 separate SQL statement if you need to delete data from 2 separate tables. But the query you posted seems inter dependent. So once you delete data from Table1, you’ll loss the key values matching to Table2 for deletion.

So, I’d suggest you to either use referential integrity or create a trigger on Table1 to delete matching records from Table2 simultaneously.

Here’s an example.

1. --Create Table1
CREATE TABLE Table1
(
                                        EId int,
                                        EName varchar(16)
);

2. --Create Table2
CREATE TABLE Table2
(
                                        EId int,
                                        EName varchar(16)
);

3. --Create Trigger Function to DELETE matching records from Table2
CREATE FUNCTION trg_fn_Table1_After_Delete()
RETURNS TRIGGER AS
$BODY$
BEGIN
DELETE FROM Table2 WHERE EId=Old.Eid;
RETURN NEW;
END $BODY$ LANGUAGE PLPGSQL

4. --Create Trigger on Table1 AFTER DELETE which will execute the function/mathod trg_fn_Table1_After_Delete()
CREATE TRIGGER trg_Table1_After_Delete
AFTER DELETE
ON Table1
FOR EACH ROW
EXECUTE PROCEDURE trg_fn_Table1_After_Delete();

----------------------Insert sample records into Table1
insert into Table1(EId,EName)
values('1','Rajesh');
insert into Table1(EId,EName)
values('2','Ranjan');
insert into Table1(EId,EName)
values('3','Alok Kuwnar'); ---NonMatching Record
----------------------Insert sample records into Table2
insert into Table2(EId,EName)
values('1','Rajesh');
insert into Table2(EId,EName)
values('2','Ranjan');
insert into Table2(EId,EName)
values('4','Ranjan Ranjan');---NonMatching Record

----------------------Validate data
select * from Table1;
select * from Table2;
---------------------Delete data from Table1

DELETE FROM table1
USING table2
WHERE table2.EId = table1.EId
AND Table1.EId=1;

----------------------Re-Validate data
select * from Table1;
select * from Table2;

Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *