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 ?
+2
A:
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
2010-05-19 14:03:05
There is no identity column available.All column values are same
Shyju
2010-05-19 14:04:25
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
2010-05-19 14:06:54
+2
A:
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
2010-05-19 14:13:02