CODEWITHSUNDEEP
mysql
Jeff
Jeff

Reputation: 467

MySQL delete join with multiple ON fields

I'm having issues creating a statement to delete records with a join on multiple fields. I've searched and found many solutions, that are not working for me still.

I have two tables: orders.profile and orders.qualified

There isn't a simple one single field to join between, but multiple. Essentially based on the customers needs some of the items stored in their profile may not qualify based on geography, so they need to be removed.

Here's what I've been playing with, currently I'm getting UNKNOWN TABLE error, when I remove the database from the table definition at the beginning, I get a NO DATABASE SELECTED error.

DELETE FROM orders.profile
USING orders.profile AS p
LEFT OUTER JOIN orders.qualified AS q
ON p.cust_id = q.cust_id
AND p.job_number = q.job_number
AND p.job_seg = q.job_seg
AND p.zip = q.zip
AND p.carrier_route = q.carrier_route
WHERE p.cust_id = {the cust_id in question}
AND p.job_number = {the job number in question}
and q.qualified = 0;

Upvotes: 0

Views: 208

Answers (2)

Jeff
Jeff

Reputation: 467

Based on the answer from Holger Brandt I was able to get this working by defining the database and the alias.

So the query should look like this:

DELETE orders.p
FROM orders.profile AS p
LEFT OUTER JOIN orders.qualified AS q
ON p.cust_id = q.cust_id
AND p.job_number = q.job_number
AND p.job_seg = q.job_seg
AND p.zip = q.zip
AND p.carrier_route = q.carrier_route
WHERE p.cust_id = {the cust_id in question}
AND p.job_number = {the job number in question}
and q.qualified = 0;

Upvotes: 1

Holger Brandt
Holger Brandt

Reputation: 4354

Try the delete without the USING statement:

DELETE p FROM
orders.profile AS p
  LEFT OUTER JOIN orders.qualified AS q
  ON p.cust_id = q.cust_id ...

If this doesn't work, there is something more fundamental going on.

Upvotes: 1

Related Questions