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.