Update with a subquery

Well, if you're using SQL Server 2012, I suggest MERGE command and OUTPUT clause, e.g.

;with cteNeedChanges as (select P.* from dbo.Products P 
inner join dbo.Categories C ON P.CategoryID = C.CategoryID
where C.CategoryName = 'Confections' and
 P.UnitsInStock > 50 and P.UnitPrice <> 10)
MERGE Products as Target 
USING cteNeedChanges as Source ON Target.ProductID = Source.ProductID
WHEN Matched THEN UPDATE SET UnitPrice = 10
OUTPUT Deleted.ProductID, Deleted.UnitPrice, Inserted.UnitPrice

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles