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 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2006-10-03 : 10:49:50
|
| I have peculiar situation here with my tablesINSTRUMENTBOOK PAGE IID___________________0013 0014 10013 0014 2NAMESBOOK PAGE SEQ ROLE Id iid___________________________________________0013 0014 1 1 1 10013 0014 2 3 2 10013 0014 1 1 3 20013 0014 2 3 4 2I want to join INSTRUMENT and NAMES table based on book and page columns to update IID column.But my problem I have is there are 2 sets of duplicate rows in the names table (0013, 0014, 1, 1 and 0013, 0014, 2, 3) one set should be updated a value of 1 for IID and 2nd set should be update with a value of 2Is there any way to do thisany suggestions and inputs would helpThanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-03 : 11:19:11
|
Something like this:Update NSet IID = (case when N.Seq = 1 and N.Role = 1 then 1 when N.Seq = 2 and N.Role = 3 then 2 end)From Names N join Instrument Ion N.Book = I.Bookand N.Page = I.Page This is ugly solution which contains hardcoded values...if you want to avoid that, you will have to change table design of INSTRUMENT table to include SEQ and ROLE fields as well.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
|
|
|
Complex update