Update table rows in SQL Server 2000

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

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Back to top