Delete from table when field does not match another table in MySQL

Question

I'm trying to use the following SELECT statement to craft a DELETE statement after I get it working.

Basically table "listing" has a bunch of record that need to be removed. If the EmpNo cannot be found in address table then I want to remove the record from listing table. I keep getting a invalid syntax. What am I doing wrong?

SELECT A.*
FROM address A
LEFT JOIN listing B
USING (EmpNo)
WHERE B.EmpNo IS <> A.EmpNo

In the future, please post the error message. It is pretty easy to spot this time, but isn't always. And MySQL 1064 errors typically point to exactly the place where the error occurs. — Michael Berkowski
– Michael Berkowski, Commented Feb 26, 2013 at 20:15
Okay, will do. This one just gave a really generic error so I assumed I had really messed up the query. — Rocco The Taco
– Rocco The Taco, Commented Feb 26, 2013 at 20:27

2 revs · Accepted Answer · 2013-02-28 11:57:05Z

2

Remove the IS keyword here:

WHERE B.EmpNo IS <> A.EmpNo

Should be:

WHERE B.EmpNo  <> A.EmpNo

If EmpNo exists with the same name in both tables, USING will work correctly there. Otherwise, you can be a little more explicit with ON:

FROM 
  address A
  LEFT JOIN listing B
    ON A.EmpNo = B.EmpNo

To find those records in A with no match in B, test for B.EmpNo IS NULL instead of B.EmpNo <> A.EmpNo.

WHERE B.EmpNo IS NULL

edited Feb 28, 2013 at 11:57

community wiki

2 revs
Michael Berkowski

Sign up to request clarification or add additional context in comments.

2 Comments

Rocco The Taco Over a year ago

Hmmm..I am trying this and it is deleting all records in listing, even the ones where there is a match with address? DELETE A.* FROM address A LEFT JOIN qemplisting B ON B.EmpNo = A.EmpNo

Michael Berkowski Over a year ago

@RoccoTheTaco Sorry I missed this comment. To delete only those not matching, you need WHERE B.EmpNo IS NULL - meaning there's a record in A which has no matching record in B.

Collectives™ on Stack Overflow

Delete from table when field does not match another table in MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related