update bulk of rows with different conditions

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

update bulk of rows with different conditions

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-21 : 19:08:34

Hi people,

Code like this:
Update table1 set code='123' where score between 100 and 120
If I have 100 records with 'code' to update, is there any easier way, for example, using case or directly access the table2 where code and score data are saved? Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-21 : 21:07:20

Are you asking how to join table2 into your query? If so, here's a quick example:

update t1
set c3 = t2.c3
from table1 t1
join table2 t2
on t1.c1 = t2.c1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-21 : 21:35:12

Thanks but this is not what I need. My scenario is:
update table set code='123' where score between 100 and 120
update table set code='223' where score between 140 and 180
...
update table set code='343' where score between 200 and 220
If I have 100 to update is there any easier way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:08:35

why not store the values in another table like


table2
--------------
startrange,endrange,value
100       , 200    ,  123
140       , 180    ,  223
...

and then use table in join like what Tara showed


update t1
set t1.code= t2.[value]
from table1 t1
join table2 t2
on t1.score between t2.startrange and t2.endrange

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources