Re: UPDATE with subquery too slow

Eric Jain wrote:
> I can't get the following statement to complete with reasonable time.
> I've had it running for over ten hours without getting anywhere. I
> suspect (hope) there may be a better way to accomplish what I'm trying
> to do (set fields containing unique values to null):

[...]

> Using EXISTS rather than IN (I'm using 7.4-RC2, not sure if IN queries
> were already improved in this release):
>
> UPDATE requests
> SET session = NULL
> WHERE NOT EXISTS
> (
> SELECT r.session
> FROM requests r
> WHERE
> r.session = session
> AND NOT r.id = id
> );

I suppose you could try:

UPDATE requests
SET session = NULL
WHERE EXISTS
(
SELECT r.session
FROM requests r
WHERE
r.session = session
GROUP BY r.session
HAVING count(*) = 1
);

but I don't know that you'll get much different results than your
version.

--
Kevin Brown kevin(at)sysexperts(dot)com