I want to update a row in my date base. The problem is, through a mistake on my part, I have two identical rows of data. How do I run the update on just one row?
A:
Use SET ROWCOUNT;
SET ROWCOUNT 1; UPDATE Production.ProductInventory SET Quantity = 400 WHERE Quantity < 300; GO
Jeff Spicoli
2010-05-25 23:03:46
"Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL)."
Francisco Soto
2010-05-25 23:09:24
Nice Francisco, very important to know! Thanks!
Jeff Spicoli
2010-05-25 23:13:45
+3
A:
Often tables have a unique ID. And you should filter on that.
For example,
UPDATE YourTable
SET YourColumnToUpdate = 'your_value'
WHERE YourUniqueColumn = @Id
If your table does not have a unique ID, consider adding one: an integer column with a Primary Key and Identity.
Bill Paetzke
2010-05-25 23:04:12
+2
A:
In SQL Server 2005+, you can use
UPDATE TOP 1 ....
The advantage of this over SET ROWCOUNT is that any triggers will not be subject to a ROWCOUNT limit, which is almost certainly a good thing.
spender
2010-05-25 23:09:04
+1
A:
I suggest you go back and normalize your database. At the very least add a auto increment int primary key column and use that id. Using UPDATE TOP 1 might work and directly answers your question, but non - normalization of your database is the "real" problem.
RandyMorris
2010-05-25 23:16:13