Update With Joins and Case Statment

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)

Update With Joins and Case Statment

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-23 : 11:29:49

Hi, Im having trouble getting this to work. Basically I need to update 'YearID' column of table 'dbo.Year' based on 'Class' column value of table 'dbo.Vehicle'.

UPDATE dbo.Year
SET Y.[YearID] = CASE
WHEN V.[CLASS]='BMW' THEN Y.[YearID]=6 ELSE NULL END
WHEN V.[CLASS]='AUDI' THEN Y.[YearID]=7 ELSE NULL END
WHEN V.[CLASS]='VOLVO' THEN Y.[YearID]=8 ELSE NULL END
FROM dbo.Year Y INNER JOIN dbo.Vehicle V
ON Y.ID=V.ID

I get an Error Message 'Incorrect syntax near '='.' on Line 2

What am I missing here ?

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-03-23 : 11:43:08

UPDATE dbo.Year
SET Y.[YearID] = CASE
WHEN V.[CLASS]='BMW' THEN 6 ELSE NULL
WHEN V.[CLASS]='AUDI' THEN 7 ELSE NULL
WHEN V.[CLASS]='VOLVO' THEN 8 ELSE NULL END
FROM dbo.Year Y INNER JOIN dbo.Vehicle V
ON Y.ID=V.ID

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-23 : 11:49:24

I tried

UPDATE dbo.Year
SET Y.[YearID] = CASE
WHEN V.[CLASS]='BMW' THEN 6 ELSE NULL
WHEN V.[CLASS]='AUDI' THEN 7 ELSE NULL
WHEN V.[CLASS]='VOLVO' THEN 8 ELSE NULL END
FROM dbo.Year Y INNER JOIN dbo.Vehicle V
ON Y.ID=V.ID

But I get error message Incorrect syntax near the keyword 'WHEN'.
Line 4

I tried adding a ',' at the end of NULL but that did not help either

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 11:50:19

UPDATE dbo.Year
SET Y.[YearID] =
CASE
WHEN V.[CLASS]='BMW' THEN Y.[YearID]=6
WHEN V.[CLASS]='AUDI' THEN Y.[YearID]=7
WHEN V.[CLASS]='VOLVO' THEN Y.[YearID]=8
ELSE NULL
END
FROM dbo.Year Y INNER JOIN dbo.Vehicle V
ON Y.ID=V.ID

Jim

Everyday I learn something that somebody else already knew

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-03-23 : 11:56:03

my bad, too much of a hurry

UPDATE dbo.Year
SET Y.[YearID] = CASE
WHEN V.[CLASS]='BMW' THEN 6
WHEN V.[CLASS]='AUDI' THEN 7
WHEN V.[CLASS]='VOLVO' THEN 8 ELSE NULL END
FROM dbo.Year Y INNER JOIN dbo.Vehicle V
ON Y.ID=V.ID

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-03-23 : 11:56:43

Darn, jimf beat me to it! :)

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-23 : 12:08:07

Thank you folks,got it to work

Bruce, yours worked just fine for me. I had to just do 1 minor change, Y.[YearID] to [YearID] in 2nd line. I was getting a multi-part identifier error.

UPDATE dbo.Year
SET [YearID] = CASE
WHEN V.[CLASS]='BMW' THEN 6
WHEN V.[CLASS]='AUDI' THEN 7
WHEN V.[CLASS]='VOLVO' THEN 8 ELSE NULL END
FROM dbo.Year Y INNER JOIN dbo.Vehicle V
ON Y.ID=V.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources