Problem deleting rows using left outer join in mysql

Cant seem to workout what im doing wrong here

SELECT * FROM tbl_imagelabel LEFT OUTER JOIN tbl_image ON tbl_imagelabel.label_id = tbl_image.label_id WHERE 开发者_StackOverflowtbl_image.label_id is NULL

shows the exact rows I want to delete. but if i change SELECT * FROM to DELETE FROM it doesnt work

DELETE FROM tbl_imagelabel LEFT OUTER JOIN tbl_image ON tbl_imagelabel.label_id = tbl_image.label_id WHERE tbl_image.label_id is NULL

For futher reference, on MySQL 5+:

DELETE tbl1.* FROM tbl1 LEFT JOIN tbl2 USING(fld) WHERE tbl2.fld IS NULL;

It'll delete any orphans on TBL1 using fld as join.

GL Paulo Bueno

You're trying to delete from multiple tables in a single query with that syntax. Try something more like (and this is just a loose example, not meant to be optimized or anything):

DELETE FROM tbl_imagelabel
WHERE label_id IN (
    SELECT tbl_imagelabel.label_id 
    FROM tbl_imagelabel 
    LEFT OUTER JOIN tbl_image 
        ON tbl_imagelabel.label_id = tbl_image.label_id 
    WHERE tbl_image.label_id IS NULL
)

I believe this does the same thing without the explicit join.

DELETE FROM tbl_imagelabel 
WHERE label_id NOT IN (SELECT label_id FROM tbl_image)

DELETE FROM tbl_imagelabel 
where label_id IN
(
SELECT tbl_imagelabel.label_id FROM tbl_imagelabel 
LEFT OUTER JOIN tbl_image 
ON tbl_imagelabel.label_id = tbl_image.label_id WHERE tbl_image.label_id is NULL
)

assuming that label_id is the unique primary key.

Whilst all the answers given here provide alternative means of solving the specific example given in the question, they all do so without actually including a left outer join explicitly in the delete statement. To do that, you need the 'USING' statement as follows:

DELETE FROM til USING tbl_imagelabel as til LEFT OUTER JOIN tbl_image as ti 
ON til.label_id = ti.label_id WHERE ti.label_id is NULL

References:

• http://donauweb.at/ebusiness-blog/2008/01/21/delete-from-mysql-with-outer-join/

I used this Query here to delete orphan data:

DELETE FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE idtable1=idtable2)