🚀 go-pugleaf

Hi Paul,

You can't use joins in a delete:

I have 2 solutions in mind:

1) DELETE
      FROM "Tasks" T
     WHERE NOT EXISTS (
             SELECT *
               FROM "Servers" S
              WHERE S."ClientName" = T."ClientName"
                AND S."ServerName" = T."ServerName")

--> This isn't the fasted way to go, because the table "Tasks" will be 
scanned naturally.

2) Write a small stored procedure

create procedure delete_records_example
as
declare variable db_key CHAR(8); /* Or use PK of "Tasks" */
begin
   for
     select T.rdb$db_key
       from "Tasks" T
            left outer join "Servers" S on
                  S."ClientName" = T."ClientName"
              and S."ServerName" = T."ServerName"
      where S."ClientName" is null
        and S."ServerName" is null
     into db_key
/* Normally I test if PK of S is null, but you didn't gave this 
information. */
   do begin
     delete
       from "Tasks" T
      where T.rdb$db_key = :db_key;
   end
end

Normally this is the fasted way do delete record with complex logic.  Of 
course it depends on the execution of your select statement.

Van den Wouwer Danny
Peopleware NV

 > LEFT OUTER JOIN "Servers" S
 > ON S."ClientName"=T."ClientName" AND S."ServerName"=T."ServerName"
 > WHERE S."ClientName" IS NULL AND S."ServerName" IS NULL


Paul Embleton wrote:
> I need to delete records from one table, for which there are no 
> corresponding records in a second table. I get 'token unknown LEFT' with 
> the following:
> 
> DELETE FROM "Tasks" T
> LEFT OUTER JOIN "Servers" S 
> ON S."ClientName"=T."ClientName" AND S."ServerName"=T."ServerName"
> WHERE S."ClientName" IS NULL AND S."ServerName" IS NULL
> 
> tia
> Paul