Today I discovered that a while back on a PHP/HTML/MySQL project I made some serious mistakes on my database design. To explain it in short:
I have one a table, lets call it “table1″, and another table, lets call it “table2″. Table1 references a row from table2, but I didn’t used foreign keys in these days. (I don’t if I didn’t know that they are exist or if InnoDB wasn’t available.) The Website has an admin tool which edits all the tables through some HTML forms. My mistake was that it is possible to delete a row from table2 which leaves all the rows in table1 referencing this row in table2 somehow broken. Today someone discovered this mistake I needed to clean this up. My idea was to create a simple query which cleans table1 and the alter the PHP which deleting rows from table2 to also clean up table1.
I started which the following query:
-
DELETE FROM table1 WHERE id IN (
-
SELECT t1.id
-
FROM table1 AS t1
-
LEFT JOIN table2 AS t2 ON t1.key = t2.key
-
WHERE t2.key IS NULL
-
)
Which leads to the following error:
#1093 - You can't specify target table 'table1' for update in FROM clause
I was some kind of shocked because I had no idea whats wrong with this query. After searching the web for a while I found out that it is some kind of bug in MySQL but there is a weird looking workaround: continue reading…