Better way to update table with value from another table

Question

I'm trying to rewrite the following query to make it more efficient:

update c set c.cartype = (select Id from carTypes where carType = 'Coupe')
from cars c
inner join doorcount dc
    on c.Id = dc.carId and dc.Value=2
       and c.carType= (select Id from carTypes where carType = 'Sedan')

What I've come up with so far is:

update c set c.cartype = ct.id
from cars c
inner join cartypes ct on ct.carType='Coupe'
inner join doorcount dc on c.Id = dc.carId and dc.Value=2 
inner join carTypes ct2 on c.carType = ct2.Id and ct2.carType = 'Sedan'

This looks better to me, but is performing almost exactly the same.

Is there a more efficient way to perform this update?

Answer 1

You could also remove any doubt about the impact of the subqueries using:

DECLARE @CoupeID INT, @SedanID INT;

SELECT @CoupeID = MAX(CASE carType WHEN 'Coupe' THEN Id END), 
       @SedanID = MAX(CASE carType WHEN 'Sedan' THEN Id END)
FROM dbo.carTypes WHERE carType IN ('Coupe','Sedan');

UPDATE c SET c.carType = @CoupeID
FROM dbo.cars AS c
WHERE c.carType = @SedanID
AND EXISTS (SELECT 1 FROM dbo.doorcount AS dc
  WHERE dc.Value = 2 AND dc.carId = c.Id);

Of course appropriate indexes on specific columns mentioned here would be useful for performance (again, if you post the plans somewhere, we can probably help identify opportunities). But in general, an update is an update, and unless these tables are massive, even the slowest way to find the rows to update will pale in comparison to the cost of the update itself.

Please also always use schema prefix and be very careful about CaSe SenSiTivIty.