SQL statement to update rows in a table, insert into/update same table

I have a seemingly pretty simple situation here, but one which I have never dealt with. I have researched the issue and have found situations pretty similar, but not close enough to run on a production db.

I have 1 table with 2 columns. I want to find all entries that share the same values in column 1, and update one of the rows’ column 2. Please keep in mind that the example below is over-simplified. This is a large db with many rows in this table… i need to update a specific value in col_2 for one of the matching values in col_1.

For instance:

        col_1     col_2
        1111     green
        1111     blue
        2222     green
        2222     blue

    Becomes:
    col_1     col_2
    1111     green
    1111     red
    2222     green
    2222     red

From what I have gathered, something along the lines of an update statement but I’m at a loss for the logic of finding the matching values and updating only one of them. Any help is appreciated.

Answer

Assuming the col2 values are unique for each col1, you can chose one using join:

update t join
       (select col1, max(col2) as max_col2
        from t
        group by col1
       ) tt
       on tt.col1  t.col1
    set col2 = 'red';