I'm tryi开发者_如何学JAVAng to delete from a table, using join from other tables.

My sql is the following:

DELETE FROM `threadsread` 
USING `mybb_threads` t
WHERE
threadsread.tid=threads.tid and threadsread.uid in (2111, 2564, 2326, 2510) 
and mybb_threads.fid=30

But I get the following error:

1109 - Unknown table 'mybb_threadsread' in MULTI DELETE

None of those are views, all are real tables. And I could run a select using a similar SQL, without any problem.

Please take a look at the below from Mysql.com

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

I managed to get it working with the following sql:

delete  FROM threadsread
USING  threadsread 
 inner join threads
WHERE
threadsread.tid=threads.tid and threadsread.uid in (2111, 2564, 2326, 2510) 
and threads.fid=30

Thanks for the help M. of CA