Manipulate duplicate rows

I have two of the exact same rows, but I want to change one field of one of
the rows. How do I do this?
i.e.
table_a
group name revenue
A jim 20
A jim 20
I want to change one jim to ken.
I tried
update table_a
set name = 'ken'
from (select top 1 * from table_a) as t1
where table_a.group = t1.group
but this updates both records
On Fri, 15 Apr 2005 09:59:02 -0700, bamboo wrote:

> I have two of the exact same rows, but I want to change one field of one of
>the rows. How do I do this?
>i.e.
>table_a
>group name revenue
>A jim 20
>A jim 20
>I want to change one jim to ken.
>I tried
>update table_a
>set name = 'ken'
>from (select top 1 * from table_a) as t1
>where table_a.group = t1.group
>but this updates both records
Hi bamboo,
This really should not have happened - each table in a database should
always have a primary key. You have just found out why: there is no way
for SQL Server to distinguish one row from the other, so all operations
will affect both or none.
Here's a link to an article that discusses some techniques to clean up
the mess: http://www.aspfaq.com/show.asp?id=2431.
And here's a link to an article that explains what you can do to prevent
this from happening again: http://www.aspfaq.com/show.asp?id=2509.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)