2

I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query. I have tried various union and inner joins in an attempt to simplify it and my biggest problem is getting around the following error:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

My query below solves this but it is not very efficient.

What am I trying to achieve?

When a media item has been deleted, I wish to soft delete all products where that media is associated providing that product does not have any other media files associated.

Schema

+-----------+----------+
| entity_id | media_id |
+-----------+----------+
|       192 |        1 |
|       193 |        1 |
|       193 |        2 |
+-----------+----------+
  • I have a composite Primary Key index on entity_id and media_id
  • I have a Foreign Key constraint on entity_id to product table
  • I have a Foreign Key constraint on media_id to product_media table

When I delete media #1 I would like to be able to update entity_id #192 to set deleted_at to the current timestamp. I DO NOT want to update entity_id #193 because another media is associated to it.

UPDATE product
SET deleted_at = NOW()
WHERE id IN (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    AND entity_id NOT IN (
        SELECT entity_id
        FROM product_media
        GROUP BY entity_id HAVING COUNT(media_id) > 1
    )
)

As you can see there are two sub-queries, the fact I have 1 sub-query is somewhat of a concern but having two I know i'm doing something wrong but I cannot think for the life of my how to get around this?

1

You have an extra unnecessary subquery check, and your subquery seems to only return a rowset if there is a single value in it, so you may want to use = instead of in. You can achieve the same by doing this:

UPDATE product
  SET deleted_at = NOW()
WHERE id = (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    GROUP BY entity_id
    HAVING COUNT(media_id) = 1
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.