Update table rows in SQL Server 2000

How can I update the first row of a group?

I have a table like this structure:
col1     col2
1           row1 *
2           row1
3           row1
4           row2 *
5           row2
6           row2

I want to update the col1 field of the record with *. That means I need to update the first row of the distinct col2 field.

How can I do this with SQL Server 2000?

    Requires Free Membership to View

    Login

Is there any order to the rows at all, apart from the col2 value? If not, how would you know which row to update? If col1 does have values in it like the ones shown, you'd use an update like:

UPDATE sometable SET somecolumn = '*' WHERE col1 IN (SELECT MIN(col1) FROM sometable GROUP BY col2)

This was first published in February 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top