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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-22 : 09:05:58
|
| Anthony writes "I really need help with this. I have a table in which are two fields. The first field is an ID field and is an identifier. The second field is a char and should always be unique. This table is a lookup for other tables in the database.In an application that I have written as an editor of this data, I am creating a detached record set to represent the data in this one table. After a user makes whatever changes are necessary, I save the data back to the database. Here is the problem. Lets say you have 2 records in the table and it looks like this: ID Class 1 A 2 BNow, you want to “UPDATE” record 1 to Class = ‘B’ and also “UPDATE” record 2 so that Class = ‘A’ (There could be many cases of this in hundreds of records). I can’t just delete all records in the table and rebuild it because of the ID changing on me (and they may be referenced). Also, when I run the first update, I will get an error saying that I can’t have duplicates for the “Class” field and the transaction will fail.Is there a way of updating several records simultaneously? Something like this:"Update [TableName] Set Class = “A” where ID = 1; Set Class = “B” where ID = 2” and so on. (I know this statement here won’t work, but it conveys what I want to do)Obviously, using two “Update” statements at a time in this case will generate an error on the first execute (even though that if both were run there wouldn’t be any duplicates)Thanking you in advance, Anthony Queen" |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-04-22 : 11:50:05
|
| Update TableName Set Class = case when id = 1 then 'B' when id = 2 then 'A' end where id in (1,2) |
 |
|
|
|
|
|
Updating multiple (unique) records . (sounds easy)