Update on row for each group with max value in other column

Question

I have to update a column in a very, very large table. So performance is a big issue.

The question is the similar to this one, but only for Sybase Adaptive Server Enterprise (ASE 12.5.4) plus I need to do an update on the retrieved rows. Is there a way to do it without a self join like in the top voted answer for Oracle?

This was the first attempt with a join, but it is by orders to slow for the table it is intended for:

UPDATE table SET flag = 1
FROM table AS a1
LEFT OUTER JOIN table AS a2
ON (a1.groupId = a2.groupId AND a1.id < a2.id)
WHERE a2.groupId IS NULL
and a1.somename in ('x', 'y')

Brian Hooper · Answer 1 · 2010-07-14T11:01:55.540

1

Would...

UPDATE table AS t1 SET flag = 1
    WHERE t1.somename IN ('x', 'y') AND
          t1.id = (SELECT MAX (t2.id)
                    FROM table t2
                    WHERE t2.groupId = t1.groupId);

be any help?

edited Jul 14 '10 at 11:01

answered Jul 14 '10 at 10:27

Brian Hooper

21,544
24
88
139

1

I think you're missing a correlation name ` WHERE t2.groupId = t1.groupId` or something? – Martin Smith Jul 14 '10 at 10:30
Hmmm. I didn't think I'd need it, but you're probably right; I'll edit accordingly. – Brian Hooper Jul 14 '10 at 11:00

Update on row for each group with max value in other column

1 Answers1