How to delete master records with a single sql?

Question

Using Delphi 7 and interbase 7

Can I delete a master data record and all its nested detail records in a single SQL statement?

Example:

Table1
ID - Integer
TITLE - Varchar (80)

Table2
ID - Integer
Table1_ID - Integer
TITLE - Varchar (80)

Table3
ID - Integer
Table2_ID - Integer
TITLE - Varchar (80)

I would like to remove ID 10 from table 1 and all its comparable records (Table1_ID) in table 2 and all its comparable records (Table2_ID) in table 3

If I cannot do it in one sql, how can I do it in several sqls (the correct sequence for call statements)?

Answer 1

DELETE FROM Table3 WHERE Table2_ID IN (SELECT ID FROM Table_2 WHERE Table1_ID = xxxx)

DELETE FROM TABLE 2 WHERE Table1_ID = xxxx

DELETE FROM TABLE 1 WHERE ID = xxxx

Answer 2

Yo can do this with some SQL removal in order, writing to Table3, table2 and table1. All in one transaction do this as a "unique operation."

One option is to use triggers to record the deletion associated with table 2 when deleting one record in table1 and equivalent in table2 to delete related records in table3.

Another (if DB allows you) to use ON CASCADE DELETE (or similar) to delete related entries on tabla2 and table3 when deleting an un table1 entry (see the help or documentation for your SGBD / database).

Sorry for my mistakes with English. This is not my natural language.

Sincerely.

Answer 3

You can use foreign keys to cascade deletion of child records when deleting a parent record.

The next command is to create a foreign key to delete records in table 2 (Table1_ID) when the record in table 1 (ID) is deleted.

ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2_TABLE1 FOREIGN KEY (Table1_ID) REFERENCES TABLE1 (ID) ON DELETE CASCADE; 

This way the database engine takes care of deletion in child tables, so all you have to do is

 delete from TABLE1 where ID = :ID_VALUE 

Hello,

Answer 4

You cannot do this in one statement, but instead, you can use transactions in Delphi to make sure that either all or none of the statements are executed.

If you are using BDE, cancel the TDatabase component and set the default properties and write the following code.

 try Database1.StartTransaction; //Execute first query //Execute second query //Execute third query Database1.Commit; except on E: Exception do Database1.Rollback; end; 

If you use ADO, use the ADOConnection1.BeginTrans; instructions ADOConnection1.BeginTrans; ADOConnection1.CommitTrans; ADOConnection1.RollbackTrans;

Answer 5

If you create foreign key references with the cascade parameter, deleting the parent entry will also delete all the details (unless other restrictions prevent this).

SQL:

 ALTER TABLE Table2 ADD CONSTRAINT Table2_Table1_ID FOREIGN KEY(Table1_ID) REFERENCES Table1(ID) ON DELETE CASCADE 

This solution does not require collaboration of client applications, the server will maintain data model consistency.

Answer 6

In addition to the answers already provided with several SQL statements, foreign key relationships, and triggers. In InterBase, you can also record a stored procedure to delete master and detail records. Then you need only one SQL statement in your program.

Below are two types of stored procedures that you can use in this situation.
The first one is almost the same as HeartWave's answer, but then in a stored procedure.

 CREATE PROCEDURE DELETEMASTERDETAIL_WITHOUTINFO( pMasterID INTEGER) RETURNS ( rResult INTEGER) AS declare variable vTable2ID integer; begin /* don't return information about deleted records */ rResult = 0; for select id from table2 where table1_id = :pMasterID into :vTable2ID do begin delete from table3 where table2_id = :vTable2ID; end delete from table2 where table1_id = :pMasterID; delete from table1 where id = :pMasterID; rResult = rResult + 1; suspend; end 

The SQL statement to call this stored procedure:

 select rresult from deletemasterdetail_withoutinfo(:pMasterID) 

The second returns information about the number of deleted records in the table. I don’t know if you need it, but maybe it is useful for someone else. If the identifier field in table 1 is the primary key, the first for the select statement is a little redundant.

 CREATE PROCEDURE DELETEMASTERDETAIL_WITHINFO( pMasterID INTEGER) RETURNS ( rTable1Deleted INTEGER, rTable2Deleted INTEGER, rTable3Deleted INTEGER) AS declare variable vTable1ID integer; declare variable vTable2ID integer; declare variable vTable3ID integer; begin /* return information about deleted records */ rTable1Deleted = 0; rTable2Deleted = 0; rTable3Deleted = 0; for select id from table1 where id = :pMasterID into :vTable1ID do begin for select id from table2 where table1_id = :vTable1ID into :vTable2ID do begin for select id from table3 where table2_id = :vTable2ID into :vTable3ID do begin rTable3Deleted = rTable3Deleted + 1; delete from table3 where id = :vTable3ID; end rTable2Deleted = rTable2Deleted + 1; delete from table2 where id = :vTable2ID; end rTable1Deleted = rTable1Deleted + 1; delete from table1 where id = :vTable1ID; end suspend; end 

The SQL statement to call this stored procedure:

 select rtable1deleted, rtable2deleted, rtable3deleted from deletemasterdetail_withinfo(:pMasterID) 

BTW. I almost always use at least one return parameter in SP. This will allow you to use the Query component to call the stored procedure.
If there are no result parameters, you must use the stored procedure component to execute SP.