How to delete master detail records using one sql?

Using Delphi 7 and interbase 7

Is it possib开发者_如何学运维le to delete a master detail record and all of its nested detail records in one 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 delete ID 10 from Table1, and all of its matching records (Table1_ID) in table 2, and all its matching records (Table2_ID) in table 3

If i can't do this in one sql, how do i do it in multiple sqls (correct sequence to call statements)?

Yo can do it witn some SQL's deleteing in order, records on Table3, table2 and table1. All in one transaction to do it like "unique operation".

One alternative is use Triggers for delete record related on table2 when you delete one record on table1, and equivalent in table2 to delete related records on table3.

Another (if DB let you) is use ON CASCADE DELETE (or similar) to delete related records on a tabla2 and table3, when you delete a record un table1 (see help or documentation on your SGBD/Database).

Excuse-me for mistakes with English. It's not my natural language.

Regards.

You can use foreign keys to cascade deletes to child records when the parent record is deleted.

The following command should create the foreign key to delete records in Table2 (Table1_ID) when the record in Table1 (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 deletes in child tables so all you need to do is

delete from TABLE1 where ID = :ID_VALUE

Regards,

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

DELETE FROM Table2 WHERE Table1_ID=xxxx

DELETE FROM Table1 WHERE ID=xxxx

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

If you are using BDE, then drop 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 are using ADO then use ADOConnection1.BeginTrans; ADOConnection1.CommitTrans; ADOConnection1.RollbackTrans; statements

If you create foreign key references with cascade option, deleting the parent record will also delete all 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 the cooperation of the client applications, the server will keep the data model consistent.

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

Below two kind of stored procedures you could use in this situation.
The first one is almost the same as HeartWave 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 is:

select rresult
from deletemasterdetail_withoutinfo(:pMasterID)

The second one will return information about the amount of deleted records per table. I don't know if you need it, but perhaps it is helpfull for someone else. If the ID field in Table1 is the primary key the first for select statement is a bit overkill.

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 is:

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

BTW. I almost always use at least one return parameter in SP's. This will allow to use a Query component to call the stored procedure.
If there are no result parameters a Stored Procedure component must be used to execute the SP.