CODEWITHSUNDEEP
mariadb
Gavin Baumanis
Gavin Baumanis

Reputation: 403

DELETE from same table used in the WHERE

I have the following query that is supposed to delete from a table with a circular FK requirement;

DELETE FROM question 
WHERE defaultGotoQuestionId IN (
    SELECT id from question WHERE facilityId IN (
        SELECT id FROM facility WHERE customerId NOT IN (1,76)
        )
    );

But I get the following error;

Table is specified twice, both as a target for 'DELETE' and as a separate source for data

I understand the error message - that I can't DELETE from a TABLE that I am specifying in the WHERE - I just can't seem to work out how to solve it, for myself. I saw a few examples of using a join - but perhaps I am having an off day - because I have been iterating through copy/paste examples - but still can't manage it.

It works as a SELECT : In that the result gives me the records I want to delete;

SELECT id FROM question 
WHERE defaultGotoQuestionId IN (
    SELECT id from question WHERE facilityId IN (
        SELECT id FROM facility WHERE customerId NOT IN (1,76)
        )
    );

Thanks for any help!

Upvotes: 0

Views: 105

Answers (1)

wchiquito
wchiquito

Reputation: 16551

What version of MariaDB are you using?

DELETE :: Same Source and Target Table

Until MariaDB 10.3.1, deleting from a table with the same source and target was not possible. From MariaDB 10.3.1, this is now possible.

See dbfiddle.

In older versions of MariaDB (or in MySQL, for example), one option you can use is:

DELETE
  `question`
FROM
  `question`
  INNER JOIN (
    SELECT
      `id`
    FROM
      `question`
    WHERE  
      `defaultGotoQuestionId` IN (
        SELECT
          `id`
        FROM
          `question`
        WHERE
          `facilityId` IN (
            SELECT
              `id`
            FROM
              `facility`
            WHERE
              `customerId` NOT IN (1, 5)
          )
      )
  ) `der` ON
    `question`.`id` = `der`.`id`;

See dbfiddle.

Upvotes: 1

Related Questions