I need to write a a query to update col3 to 9 for each unique id _but_ only
the record for that id that has the max col2 value

col1(id)    col2    col3
1    2    4
1    1    4
2    1    4
2    2    4
3    1    4
3    3    4
3    4    4

--so after the update it looks like this...

col1 (id)    col2    col3
1    2    9
1    1    4
2    1    4
2    2    9
3    1    4
3    3    4
3    4    9

I am stumped. Can anyone help?

Here is some ddl code...

IF object_id('tempdb..#t') is NOT NULL
    DROP TABLE #t
CREATE TABLE #t
    (
    col1 int
    ,col2 int
    ,col3 int
    )
GO
INSERT #t VALUES (1 ,2,4)
INSERT #t VALUES (1 ,1,4)
INSERT #t VALUES (2 ,1,4)
INSERT #t VALUES (2 ,2,4)
INSERT #t VALUES (3 ,1,4)
INSERT #t VALUES (3 ,3,4)
INSERT #t VALUES (3 ,4,4)

SELECT * FROM #t

Dave wrote:
UPDATE #t
SET col3 = 9
WHERE col2 = (  SELECT MAX(col2)
                          FROM    #t t2
                          WHERE   t2.col1 = #t.col1 )

Thanks

Show quoteHide quote

random select a row based a weight column.
Timeout expire
Optimize this query
How can I insert a new field between existing field?
store results over network