Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2008-02-22 : 10:53:30
|
| How do I tell an update query to do the update only to one row in the table, and if there s more than one row in the where clause, then not to do the update.Something like this:update top 1 set myCol='value' where searchCol='criteria'But that causes an error. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-22 : 11:08:13
|
quote:
Originally posted by rtutus
How do I tell an update query to do the update only to one row in the table, and if there s more than one row in the where clause, then not to do the update.Something like this:update top (1) TableName set myCol='value' where searchCol='criteria'But that causes an error.
change like this & try |
 |
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-02-22 : 11:12:47
|
| visakh16 is right, you have to name the table in your statement. But if you only want to do the update where there's 1 row that satisfies your criteria, then try this:update TableName set myCol='value' where searchCol='criteria' and 1 = (select count(*) from TableName where searchCol='criteria') |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-23 : 01:01:24
|
| or good old stuffSET ROWCOUNT 1update set myCol='value' where searchCol='criteria'SET ROWCOUNT 0MadhivananFailing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-23 : 18:47:56
|
| [code]update TableNameset MyCol = 'NewMyColValue'where TableName.PrimaryKeyID in ( select PrimaryKeyID = min(a.PrimaryKeyID) from TableName a where a.searchCol='criteria' having count(*) = 1 )[/code]CODO ERGO SUM |
|
|
|
|
|
|
Update only one record