CODEWITHSUNDEEP
mysqlsqlselectsubquerysql-delete
Tom Leese
Tom Leese

Reputation: 19729

DELETE FROM with named subquery

I have a SQL query that looks like this:

DELETE
    price.*
FROM
    price
JOIN
    service
ON
    price.service_id = service.id
WHERE
    price.country_from_id NOT IN
        (SELECT
            country_id
        FROM
            carrier_zone_country
        JOIN
            carrier_zone
        ON
            carrier_zone_id = carrier_zone.id
        WHERE
            carrier_zone.carrier_service_id = service.carrier_service_id)
    OR
    price.country_to_id NOT IN
        (SELECT
            country_id
        FROM
            carrier_zone_country
        JOIN
            carrier_zone
        ON
            carrier_zone_id = carrier_zone.id
        WHERE
            carrier_zone.carrier_service_id = service.carrier_service_id)

I was hoping to avoid running the subquery twice by moving it into the FROM clause and giving it a name. However, that gave me syntax errors. Looking at the documentation, I can see that only the SELECT FROM clause can have a named subquery in it.

Firstly I am wondering why that is the case? And secondly, how could I re-write this SQL query to avoid performing the same subquery twice.

Upvotes: 0

Views: 51

Answers (1)

jarlh
jarlh

Reputation: 44795

Do one single NOT EXISTS sub-query, where both to and from countries are checked:

DELETE
    price.*
FROM
    price
JOIN
    service
ON
    price.service_id = service.id
WHERE
NOT EXIST(SELECT
            1
        FROM
            carrier_zone_country
        JOIN
            carrier_zone
        ON
            carrier_zone_id = carrier_zone.id
        WHERE country_id IN (price.country_from_id, price.country_to_id)
          AND carrier_zone.carrier_service_id = service.carrier_service_id))

Upvotes: 1

Related Questions