Want to run update query for all rows in tbl

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 2008 Forums

Transact-SQL (2008)

Want to run update query for all rows in tbl

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-30 : 07:09:21

I want to run the below update on all rows in the table lw_pos_chg, it has 9k rows.
want to fill pos_stat_desc column value based on pos_stat value in the same table.

Update lw_pos_chg
if pos_stat=1 then set pos_stat_desc='Open'
else if pos_stat = 2 then set pos_stat_desc= 'in progress'
else if pos_stat = 3 then set pos_stat_desc= 'closed'
else set pos_stat_desc= 'Suspend'

Thank you very much for the helpful info.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-30 : 08:19:51

The IF construct is used for control flow. You cannot use that in this context. Instead use a case expression like shown below:

UPDATE lw_poschg SET
	pos_stat_desc =
		CASE 
			WHEN pos_stat = 1 THEN 'Open'
			WHEN pos_stat = 2 THEN 'in progress'
			WHEN pos_stat = 3 THEN 'closed'
			ELSE 'Suspend'
		END;

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-30 : 09:21:43

quote:
Originally posted by James K

The IF construct is used for control flow. You cannot use that in this context. Instead use a case expression like shown below:

UPDATE lw_poschg
SET pos_stat_desc = CASE
WHEN pos_stat = 1 THEN 'Open'
WHEN pos_stat = 2 THEN 'in progress'
WHEN pos_stat = 3 THEN 'closed'
ELSE 'Suspend'
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources