how to update another table before relevant data for update gets deleted from current table

Question

I am deleting records from the table 'posoja' with this :

procedure TForm2.cxButton3Click(Sender: TObject);
begin
DataModule3.FDTransaction1.StartTransaction;
DataModule3.DEL_POSOJA.ExecSQL;
DataModule3.FDTransaction1.Commit;
DataModule3.POSOJA.Refresh;
end;

Property of the DEL_POSOJA Query is this :

delete from POSOJA where VRNJENO IS NOT NULL;

So all records that do have 'vrnjeno' filled, get deleted.

Now, just before I delete filled records I want to update my 'filmi' table. The query is this:

while not POSOJA.EOF do begin
FDTransaction5.StartTransaction;
try
UPD_NA_VOLJO_AFT_DEL.Close;
UPD_NA_VOLJO_AFT_DEL.SQL.Clear;
UPD_NA_VOLJO_AFT_DEL.SQL.Text := 'update filmi set na_voljo=1 where dvd_id=:a1';
UPD_NA_VOLJO_AFT_DEL.Params.ParamByName('a1').asString := DataModule3.POSOJA.FieldByName('DVD_ID').AsString;
UPD_NA_VOLJO_AFT_DEL.ExecSQL;
POSOJA.Next;
FDTransaction5.Commit;
except
on E: Exception do  begin
      ShowMessage(E.Message);
      DataModule3.FDTransaction5.Rollback;
end;
end;
DataModule3.FILMI.Refresh;
end;

My problem is that I do not know where to put these lines. I tried to put it on before execute of the DEL_POSOJA query (it works OK) but the query executes so even if 'vrnjeno' is not null,UPD_NA_VOLJO_AFT_DEL Query will update the 'filmi' table.

I cant put it before delete of 'posoja' table because it only catches events used by dbnavigator.

Problem is that I need those about to be deleted values in order to update my table.

Any ideas how to resolve this ? Got a toothache and cant think strait... :)

Answer 1

Try to make update set in one action using IN function:

UPDATE `filmi` SET na_voljo=1 WHERE `filmi`.`dvd_id` IN(SELECT `DVD_ID` FROM `posoja`)

Use proper table name for POSOJA. You will not need to navigate this table (NEXT, NEXT etc...).