UPDATE Multiple Rows Using CASE in SQL 2008

Question

Hi,

I have the following sql statement where i'm trying to update multiple rows matching a select statement.

UPDATE Cars
SET engineSize = CASE specCode WHEN 1 THEN value ELSE engineSize END
FROM Cars
INNER JOIN CarSpecs ON CarsSpecs.carID = Cars.carID

My tables are as follows:

Cars

carID   engineSize ...
1       0
2       0

CarSpecs

carID   specCode    value
1       1           1800
1       2           Blue
1       3           Petrol
2       1           2200
2       2           Green
2       3           Petrol

specCode relates to a type of specification I want to update in the Cars table (1 being the engine size)

When I run the query it comes back NULL everytime. The way I see it it should find the specCode = 1 and set the engineSize to 1800 then after it's set it just sets it to the first found value.

Any ideas?

Edit: I need to update multiple columns in Cars table. That's the reason for using CASE, ie:

UPDATE Cars 
SET engineSize = CASE specCode WHEN 1 THEN value ELSE engineSize END,
colour = CASE specCode WHEN 2 THEN value ELSE colour END
FROM Cars
INNER JOIN CarSpecs ON CarsSpecs.carID = Cars.carID

Answer 1

It looks like you don't need a CASE anyway. Does this work?

UPDATE Cars
SET Cars.engineSize = CarsSpecs.value 
FROM Cars
INNER JOIN CarSpecs ON CarsSpecs.carID = Cars.carID AND  CarsSpecs.specCode =1

The PIVOT solution (if needed)

UPDATE Cars 
SET engineSize = ISNULL([1],engineSize),
colour = ISNULL([2],colour)
FROM Cars
INNER JOIN 
(SELECT * FROM 
dbo.CarSpecs 
PIVOT (MAX(VALUE) FOR  SPECCODE IN ([1],[2])) p) Pivoted ON Pivoted.CARID = Cars.CarId

Answer 2

To update multiple columns, in your case you need multiple joins (as sketched by Martin)

UPDATE Cars
SET  engineSize = CarSpecs1.value, colour = CarSpecs2.value
FROM Cars
INNER JOIN CarSpecs CarSpecs1 
    ON CarsSpecs1.carID = Cars.carID AND CarsSpecs1.specCode =1
INNER JOIN CarSpecs CarSpecs2 
    ON CarsSpecs2.carID = Cars.carID AND CarsSpecs2.specCode =2

Use OUTER joins of not every spec is stored for each car.

Answer 3

In case that Martin's solution does not work (when it is run three times) you could do it also at once ... although a bit more arkward:

UPDATE Cars
SET Cars.engineSize = (SELECT value FROM CarSpecs WHERE CarSpecs.carId = Cars.carID AND CarSpecs.specCode = 1),
SET Cars.colour = (SELECT value FROM CarSpecs WHERE CarSpecs.carId = Cars.carID AND CarSpecs.specCode = 2),
SET Cars.fuel = (SELECT value FROM CarSpecs WHERE CarSpecs.carId = Cars.carID AND CarSpecs.specCode = 3)
FROM Cars