I want to update multiple rows with difff data

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

SQL Server 2000 Forums

SQL Server Development (2000)

I want to update multiple rows with difff data

Author

Topic

tonyinbristol
Starting Member

3 Posts

Posted - 2009-07-24 : 11:00:28

I have a simple update statement, like this:

update tablename set field1 = 'W9783345' where field2 = '0206863944' and field3 = 'Active'

I want to do several of these, but the only element that is the same is the field3 = 'Active' - the rest are all different. For example:

update tablename set field1 = 'M1093979' where field2 = '1020506073' and field3 = 'Active'
update tablename set field1 = 'W6806654' where field2 = '5243086077' and field3 = 'Active'

At the moment, I have to submit each line one at a time. There MUST be a quicker and easier way of doing it. I have tried parentheses, AND statements, all kinds of ways.

I am using WinSQL on data in a DB2 database

Thanks!!!

Tony

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 11:16:27

Try


update tablename 
	set field1 = case 
					when field2 = '0206863944' then 'W9783345' 
					when field2 = '1020506073' then 'M1093979'
					when field2 = '5243086077' then 'W6806654'
				else
					filed1
				end
where field3 = 'Active'

Madhivanan

Failing to plan is Planning to fail