DELETE using subquery

[Katrina Owen]

I inherited the maintenance of a monolithic web application with a MySQL 4.1 backend.

I am trying to clean out orphaned records (which are counted in the thousands, unfortunately).

I've been able to determine exactly which records should go using the following type of statement:

SELECT t1.field
FROM tableOne AS t1
LEFT OUTER JOIN tableTwo AS t2
ON t1.field = t2.field
WHERE t2.field IS NULL;

However, I haven't been able to construct any DELETE statements that my database will process.

I've tried:

DELETE FROM tableOne
WHERE field
IN
(
    SELECT DISTINCT t1.field
    FROM tableOne AS t1
    LEFT OUTER JOIN tableTwo AS t2
    ON t1.field = t2.field
    WHERE t2.field IS NULL
)

Since google says that MySQL 4.1 doesn't support deleting with a subquery that selects from the same table as you are deleting from, I also tried:

DELETE FROM tableOne
WHERE field
NOT IN
(
    SELECT field
    FROM tableTwo
);

In both cases, the error message says I have a syntax error at the location where my subquery starts.

Any suggestions as to what I can try next?

Katrina
[ January 13, 2008: Message edited by: Katrina Owen ]

[Katrina Owen]

I ended up using temporary tables to store the rows I needed:

CREATE TABLE tmptable 
SELECT t1.* FROM table1 as t1 
LEFT JOIN table2 as t2 ON t2.id = t1.id 
WHERE t2.id is NULL;
 
DELETE FROM table1 USING tmptable, table1 
WHERE table.id = tmptable.id;
 
DROP TABLE tmptable;

[David O'Meara]

Drat, I was about to suggest the same, but got pulled away for an hour...

[David O'Meara]

did you write that information back to the MySQL docs?

[Katrina Owen]

No, good point. Not sure where the best place would be... under DELETE, simply?

http://dev.mysql.com/doc/refman/4.1/en/

Huh. Someone there ran into something similar, and posted the following:

CREATE TEMPORARY TABLE tmptable
SELECT A.* FROM table1 AS A, table1 AS B
WHERE A.username LIKE '%2'
AND A.ID = B.ID
AND A.username <> B.username;
 
DELETE table1 FROM table1
INNER JOIN tmptable
ON table1.username = tmptable.username;

I guess next time I can use

TEMPORARY

, too

[Rob Spoor]

AFAIK, MySQL doesn't support any subqueries at all until version 5.