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)