Nested / Update Query - SQL Server Forums

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

Transact-SQL (2000)

Nested / Update Query

Author

Topic

Commcast
Starting Member

1 Post

Posted - 2012-07-24 : 20:36:09

Hi all,

I was wondering if I could get some help with a query I am trying to code.

Basically I have a table in a database called tempwebdata. This table houses the field ids for elements on a web page and the values each elements have. TempDataId is the ID for a particular page and not all pages will have the same number of elements. Example below

Basically I need a script that does the following:

Set FiedValue = 3 Where FieldId = 0002083_3525
Set FieldValue = 'Score = 3' Where FieldId = 0002083_3833
Set DisplayValue = 'Risk Rating = Low' Where FieldId = 0002083_3529
When (FieldId = 0002083_3954 and FieldValue = 0002083_101) And(FieldId = 0002083_3955 and FieldValue = 0002083_104)

**For every TempDataId which has these fields.

Variables of course will change depending on what I need to set them too.

In a rationalised table this would have been simple but given the structure of the TempWebData table it makes doing this quiet difficult.
Any help would be appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-24 : 20:55:46

If you had only the cases you described, you can do an update statement like this:

UPDATE YourTable SET
	FieldValue =
		CASE 
			WHEN FieldId = '0002083_3525' THEN '3'
			WHEN FieldId = '0002083_3833' THEN 'Score = 3'
			ELSE FieldValue
		END,
	DisplayValue = 
		CASE
			WHEN FieldId = '0002083_3529' THEN 'Risk Rating = Low'
			ELSE DisplayValue
		END;

If you had many cases it would be better to put the information into a table and then join with that table.

Edit: I missed two additional conditions you had in your posting "When (FieldId = 0002083_3954 and FieldValue = 0002083_101) And(FieldId = 0002083_3955 and FieldValue = 0002083_104)". They would go as additional WHEN..THEN segments in the first case expression.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources