Update field from another table

Question

Is there a better way to write the following simple SQL Server 2005 update statement? This just seems a bit messy inefficient.

UPDATE    QuotationItem
SET       Recurring_Cost =
          (SELECT TOP (1) Recurring_Cost
          FROM          Products
           WHERE      (Remote_ID = QuotationItem.Product_ID))
WHERE     (Quotation_ID = 115)

Thanks,

Nick

Answer 1

Is your TOP 1 really needed? If it is, since you don't specify an ordering, you've got pretty random results from your query anyway! If it is not really necessary, this will do:

UPDATE  q
SET     Recurring_Cost = p.RecurringCost
FROM    QuotationItem q
        INNER JOIN
                Products p
                ON p.Remote_ID = q.Product_ID
WHERE   q.Quotation_ID = 115

Answer 2

How About using a join

UPDATE QuotationItem 
SET Recurring_Cost = p.recurring_cost
FROM QuotationItem q join Products p on q.Product_ID = p.Remote_ID
WHERE q.Quotation_ID = 115