UPDATE Multiple rows using CASE in SQL 2008

Question

I have the following SQL statement in which I am 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:

 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 refers to the type of specification that I want to update in the Cars table (1 - engine size)

When I run the query, it returns NULL every time. As I see it, it should find specCode = 1 and set the engineSize value to 1800, after which it sets it only to the first value found.

Any ideas?

Edit: I need to update several columns in the Cars table. This is the reason for using CASE, i.e.:

 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

To update multiple columns, in your case you need several joins (as indicated 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 

The use of OUTER connections is not reserved for each specification for each vehicle.

Answer 2

If Martin’s solution doesn’t work (when it runs three times), you can do it right away ... albeit 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 

Answer 3

 UPDATE empsal SET sal = CASE WHEN sal BETWEEN 100 AND 500 THEN sal + 400 WHEN sal BETWEEN 1000 AND 2000 THEN sal + 1000 else sal END 

The ELSE part is optional. But if it is not, all cases not evaluated will be replaced by NULL.