1

I have 2 records in a table in SQL Server 2005 db which has exactly same data. I want to update one record.Is there anyway to do it?Unfortunately this table does not have an identity column and i cant use a direct update query because both will be updated since data is same.Is there anyway using rowid or something in SQL server 2005 ?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
Shyju
  • 214,206
  • 104
  • 411
  • 497

2 Answers2

5

I don't much like the TOP operator, but:

UPDATE top (1) MyTable
 set Data = '123'
 where Data = 'def'

Really, you want to have primary keys on your tables to avoid just this kind of situation, even if they are just identity surrogate values.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
2

I would add an identity column to the table and then update on that identity column or update based on whatever the primary key of the table is that makes the row unique.

Avitus
  • 15,640
  • 6
  • 43
  • 53
  • There is no identity column available.All column values are same – Shyju May 19 '10 at 14:04
  • 3
    I agree. You need to add a column that somehow makes it unique. There may not be an identity column but you have to add one. Otherwise if all the data is the same you cannot tell the update to only update one. You could possibly make it work if you wanted to create a special view or query that only returned the TOP 1 result, but that is certainly not the most straightforward way to do it. And I'm not sure it would be successful. – Thyamine May 19 '10 at 14:06