CODEWITHSUNDEEP
mysqljoin
moo moo
moo moo

Reputation: 484

How do I delete LEFT JOIN rows that are NULL in mySQL?

I am doing a LEFT JOIN like this:

SELECT mt_order_delivery_address.id, 
       mt_order.order_id, 
       mt_order_delivery_address.client_id
FROM mt_order_delivery_address
LEFT JOIN mt_order ON mt_order.order_id = mt_order_delivery_address.order_id

which then gives me a list like:

id     order_id    client_id
----------------------------
1032   NULL        382
1028   NULL        282
1020   784344      920
1002   232496      490

I want to get rid of the rows in mt_order_delivery_address where there is no corresponding order_id in mt_order. How would I do this? I can't seem to wrap my head around it.

UPDATE:

I tried this but doesn't seem to work:

DELETE mt_order_delivery_address.id FROM mt_order_delivery_address LEFT JOIN mt_order ON mt_order.order_id = mt_order_delivery_address.order_id WHERE mt_order.order_id IS NULL

Upvotes: 1

Views: 6056

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

If by "delete" you mean "don't show them in this query result" you can use an INNER JOIN instead of a LEFT JOIN. It's worth learning the different types of joins and how to use them.

If by "delete" you mean "DELETE the rows in my_order_delivery_address that have no matching my_order", you can do a multi-table DELETE:

DELETE a FROM mt_order_delivery_address AS a
LEFT JOIN mt_order AS o USING (order_id)
WHERE o.order_id IS NULL;

Upvotes: 8

Related Questions