Solved: SQL Error: Unknown table 'myTemp' in MULTI DELETE
After spending a good deal of time trying to figure out what was wrong with my query, I finally spotted the problem. After doing some research on Google and trying all sorts of suggested 'solutions', the easiest one was right in front of me.
Here is the query that was causing the error: (names have been changed to protect identity)
DELETE r1_main FROM r1_main as r1, temp1 as b
WHERE r1.id = b.id
AND DATE(b.`timeStamp`) < DATE(init_startDate)
AND DATE(b.`timeStamp`) > DATE(init_endDate);
This statement kept throwing this error in the MySQL procedure:
SQL Error: Unknown table 'r1_main' in MULTI DELETE
Solution:
DELETE r1 FROM r1_main as r1, temp1 as b
WHERE r1.id = b.id
AND DATE(b.`timeStamp`) < DATE(init_startDate)
AND DATE(b.`timeStamp`) > DATE(init_endDate);
Explaination:
I felt a bit silly after figuring out what I was doing wrong, but from the amount of results that Google returned during my search, it don't think I'm the only one who struggled with this.
If you compare the two queries, you will see that the only difference is the table alias in the DELETE clause. Once you've declared an alias for the table name, you cannot use the original table name in the query.
Hope this helps anyone else who runs across this problem.



Fantastic stupidity
thank you
I'm experiencing such
DELETE t1 FROM db1.table1 as t1 INNER JOIN db2.table2 as t2 ON t1.field=t2.fieldDo you know the solution?Thank you!
I didn't see this early enough
Working with existing code, the orioginal statement you have there works with MySQL 4.0. Migrating to MySQL 5.1 I had this bug and didn't know whats up.
I figured it out though right before I started reading your post there :). Nevertheless, I also feel really dumb.
--
Hatem
PS. In my defense I was working with someone else's code :-)
Post new comment