troubleshooting Question

Setting a column on some rows.

Avatar of Richard Quadling
Richard QuadlingFlag for United Kingdom of Great Britain and Northern Ireland asked on
MySQL ServerSQL
5 Comments1 Solution393 ViewsLast Modified:
Hi.

I have several million rows of data. Some of the rows I need to tag (del = 1).

The condition is where a column (image_url_remote) is duplicated, leaving only 1 url that is NOT del = 1.

So.

Identifying the rows ...

SELECT
    img_url_remote,
    COUNT(*)
FROM
    sw_prod
WHERE
    del = 0
    AND
    mid = 1136 -- filter by single merchant for the time being.
GROUP BY
    img_url_remote
HAVING
    COUNT(*) > 1
ORDER BY
    COUNT(*) desc;

results in the list of urls and the number of times it is used.

I now need to set all the rows to del = 1 except 1 per img_url_remote.

Regards,

Richard.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros