Home Forums SQL Server 2005 T-SQL (SS2K5) UPDATE from CTE RE: UPDATE from CTE

  • January 5, 2009 at 7:22 am

    #921111

    Philip Horan (1/5/2009)

    Thanks, not a million miles away from getting one right! 🙂

    Phil.

    Rewritten without the CTE it's much easier on the eye...

    UPDATE p SET

    SLC = (AC.AVGDirectLabour + AC.MAN + AC.SUB - AC.OH),

    SOC = AC.OH

    FROM dbo.Products p

    INNER JOIN dbo.AVGCost AC ON AC.ProductId = p.CrossReference

    WHERE Type <> 'P' AND AVGDirectLabour >0

    Note the use of "UPDATE p", and also that the table which is the UPDATE target is referenced as the FROM table.There are many possible ways to lay out this type of update, and some of them are known to be performance killers. The best known is where the UPDATE target is not the FROM table but is referenced as an explicit JOIN.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden