CODEWITHSUNDEEP
mysql
Rocco The Taco
Rocco The Taco

Reputation: 3777

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

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

Upvotes: 0

Views: 1022

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

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

Upvotes: 2

Related Questions