Problem deleting rows using left outer join in mysql

Question

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 tbl_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

Answer 1

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
)

Answer 2

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.

Answer 3

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)