Using CASE to determine field to update

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)

Using CASE to determine field to update

Author

Topic

msevast
Starting Member

2 Posts

Posted - 2005-09-25 : 16:44:17

I am attempting to take data from one table and update another table. The source table is layed out vertically and the destination is horizontally. I have constants for each field in the destination table that line up with a value from the Question_Id field in the source table. I want to perform these updates dynamically instead of writing each UPDATE separately.

I get an error:
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'CASE'.

Here is what I have so far. Any help would be greatly appreciated.

DECLARE @Base_Household_Question_ID int
SET @Base_Household_Question_ID = 1000001

WHILE (@Base_Household_Question_ID <= 1000010)

BEGIN
UPDATE tHouseHolds
SET CASE @Base_Household_Question_ID
WHEN 1000001 THEN T.M_First_Name = R.Response
WHEN 1000002 THEN T.M_Last_Name = R.Response
WHEN 1000003 THEN T.M_Address1 = R.Response
WHEN 1000004 THEN T.M_Address2 = R.Response
WHEN 1000005 THEN T.M_City = R.Response
WHEN 1000006 THEN T.M_State_Prov = R.Response
WHEN 1000007 THEN T.M_Postal_Zip = R.Response
WHEN 1000008 THEN T.Phone_Area_Code = CAST(R.Response AS varchar)
WHEN 1000009 THEN T.Phone_Number = CAST(R.Response AS varchar)
WHEN 1000010 THEN T.Correspondence_Language = THEN CAST(R.Response AS int)
END
FROM tHouseHolds T
INNER JOIN Stage_RawImagedData R
ON T.Hhid = R.Hhid
AND R.Question_Id = @Base_Household_Question_ID

END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-25 : 17:27:54

In SQL, CASE behaves as a function (i.e. only returns a value), not as a control-of-flow construct (like in VB or VBScript). Therefore it cannot control syntax of a SQL statement. You'd have to rewrite your update as follows:

UPDATE T SET
T.M_First_Name = CASE WHEN @Base_Household_Question_ID = 1000001 THEN R.Response ELSE T.M_First_Name END,
T.M_Last_Name = CASE WHEN @Base_Household_Question_ID = 1000002 THEN R.Response ELSE T.M_Last_Name END,
T.M_Address1 = CASE WHEN @Base_Household_Question_ID = 1000003 THEN R.Response ELSE T.M_Address1 END,
T.M_Address2 = CASE WHEN @Base_Household_Question_ID = 1000004 THEN R.Response ELSE T.M_Address2 END,
T.M_City = CASE WHEN @Base_Household_Question_ID = 1000005 THEN R.Response ELSE T.M_City END,
T.M_State_Prov = CASE WHEN @Base_Household_Question_ID = 1000006 THEN R.Response ELSE T.M_State_Prov END,
T.M_Postal_Zip = CASE WHEN @Base_Household_Question_ID = 1000007 THEN R.Response ELSE T.M_Postal_Zip END,
T.Phone_Area_Code = CASE WHEN @Base_Household_Question_ID = 1000008 THEN CAST(R.Response AS varchar) ELSE T.Phone_Area_Code END,
T.Phone_Number = CASE WHEN @Base_Household_Question_ID = 1000009 THEN CAST(R.Response AS varchar) ELSE T.Phone_Number END,
T.Correspondence_Language = CASE WHEN @Base_Household_Question_ID = 1000010 THEN THEN CAST(R.Response AS int) ELSE T.Correspondence_Language END
FROM tHouseHolds T
INNER JOIN Stage_RawImagedData R
ON T.Hhid = R.Hhid
AND R.Question_Id = @Base_Household_Question_ID

If this looks inefficient (and it is, but not because of the CASE), you should consider altering your database structure so that tHouseHolds is not a table but a view, which performs a cross-tab function to present the data in a horizontal format. That way, you can store the data in the vertical structure and not have to worry about the horizontal presentation; the view will automatically be updated.

msevast
Starting Member

2 Posts

Posted - 2005-09-25 : 17:48:33

Thank you for the quick response. I will give this solution a try as well as look at what can be done in the table design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources