CODEWITHSUNDEEP
mysqlsql
Milkyway
Milkyway

Reputation: 819

ERROR 1109 (42S02): Unknown table in MULTI DELETE

I use a temporary table t with these queries in order to delete duplicated rows from comment table:

CREATE TABLE t (
        id INT NOT NULL,
        hash BINARY(20),
    PRIMARY KEY(id),
    INDEX(hash) ) ENGINE=InnoDB;

INSERT INTO t (id, hash)
    SELECT id, UNHEX(SHA1(body))
        FROM `comment`;

DELETE FROM `comment`
       USING t AS t1,
             t AS t2
       WHERE t1.id = comment.id
         AND t2.hash = t1.hash AND t2.id < t1.id;

But at delete stage, I get this error:

ERROR 1109 (42S02): Unknown table 'comment' in MULTI DELETE

How can I fix it?

Upvotes: 0

Views: 2308

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

MySQL doesn't support USING in DELETE. I think you want:

DELETE c
    FROM `comment` c JOIN
         t t1
         ON t1.id = c.id JOIN
         t t2
         ON t2.hash = t1.hash AND t2.id < t1.id;

I suspect there are simpler ways to express this logic. Perhaps you should ask another question, with sample data, desired results, and an explanation of the logic you are implementing.

Upvotes: 1

Related Questions