SQL DELETE FROM several tables

I have the following tables:

dataset, links, files

dataset has a field called tiComplete, if it is 0 then the record is incomplete, links and files both have a field “biDataset” that references the record in the dataset table.

I’m trying to create a query that deletes all entries from dataset, links and files where tiComplete = 0, this is what I have:

      DELETE
            `datasets`.*,
            `links`.*,
            `files`.*
      FROM
            `datasets` `d`
      INNER JOIN
            `links` `l`
      ON
            `l`.biDataset=`d`.biPK
      INNER JOIN
            `files` `f`
      ON
            `f`.biDataset=`d`.biPK
      WHERE
            `d`.tiComplete=0;

However when I try to save the procedure that contains this I get:

SQL Error(1109): Unknown table `datasets` in MULTI DELETE

I’m using MariaDB version 10 with HeidiSQL version 11.0.0.5919

Answer

Your multiple table delete syntax is off. Use this version:

DELETE d, l, f
FROM datasets d
INNER JOIN links l ON l.biDataset = d.biPK
INNER JOIN files f ON f.biDataset = d.biPK
WHERE d.tiComplete = 0;

If you alias the tables, as you have done, then the aliases whose tables are intended for deletion should appear in the DELETE clause as a CSV list.

Note that I removed the ugly backticks everywhere, which weren’t necessary and only obfuscate the code. Also, an alternative approach here would be to look into cascading deletion. Using that approach, deletion of a record in the parent table would automatically delete all records in linked children tables.