0

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?

  • Currently that statement will only delete data from table1, never from table2. If you want to delete from two tables, you have to run two delete statements – a_horse_with_no_name Jun 28 at 12:25
2

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

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.