Multiple column update

[rani80]

I'm trying to update two rows with 1 sql statement. i don't know primary key of that table. the secondary key will result in two rows.

here is my table:

secondary key column 1 column 2
1 xx yy
1 xx yy

is there a way where i can say change the first XX to AA and the second to BB like

secondary key column 1 column 2
1 aa yy
1 bb yy

 

[amrita418]

There is no way to uniquely identify each rows given that they have all values same.

If you can add an identity column to the table then update should be easier.
something like this:

update     TableA
set        key = 'AA'
from       TableA a,
           (select   secondarykey,
                     min(identitycolumn) iden
            from     TableA 
            group by secnodarykey) b
where      a.secondarykey = b.secondarykey
           and a.identitycolumn = b.iden

You can update the other column using min.

Regards,
AA