CODEWITHSUNDEEP
sqljoinsql-updatecase
Mark Clancy
Mark Clancy

Reputation: 7889

UPDATE Multiple Rows Using CASE in SQL 2008

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

Upvotes: 4

Views: 8868

Answers (3)

Pradeep Kumar Sharma
Pradeep Kumar Sharma

Reputation: 11

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 not mandatory. But if it isn't there, all cases not evaluated will be replaced with NULL.

Upvotes: 1

AxelEckenberger
AxelEckenberger

Reputation: 16926

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

Upvotes: 1

devio
devio

Reputation: 37205

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.

Upvotes: 3

Related Questions