Update and Joins

  • JJR333

    Default port

    Points: 1469

    In the query below do I need to join tables Brs to MYCTE in the UPDATE portion of the query?

    Is there a shorter way to write this update query?

    Cheers,

    J.

    DECLARE @IDHUIS VARCHAR(4) = NULL, @IDBEW INT = NULL
    --SET @IDHUIS = 'PH'
    --SET @IDBEW = 1401

    ;WITH MYCTE AS
    (
    SELECT B.IDHuis,
    B.zzpclient_id,
    Sum( A.FreqGetal * MA.Norm / 60 ) AS [HrsWerk]
    FROM MasterActiviteitenLijst AS MA
    INNER JOIN Activiteiten AS A
    ON MA.IDHuisIDAct = A.IDHuisIDAct
    INNER JOIN Bewoners AS B
    ON B.IDHuisIDBew = A.IDHuisIDBew

    WHERE MA.GroepCode = 'HKB'
    AND (B.zzpclient_id = @IDBEW OR @IDBEW IS NULL)
    AND (B.IDHuis = @IDHUIS OR @IDHUIS IS NULL)
    GROUP BY B.zzpclient_id, B.IDHuis
    )

    UPDATE Brs
    SET HrsHKB = M.HRSWERK
    FROM MYCTE M
    WHERE M.IDHuis = Brs.IDHuis AND
    M.zzpclient_id = Brs.zzpclient_id

  • sgmunson

    SSC Guru

    Points: 110508

    The problem here is that you are trying to update a table you aren't even referencing in the UPDATE statement's FROM clause.   I'm not really sure why you're asking the question, though, as I don't see any indication of what problem you are having.   Are you getting a syntax error?   Something else?   I can only guess that your failure to join to the table being updated is at least a part of your problem.   You might also want to include a description of the objective of this update, so that we have some idea of your overall objective.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • ScottPletcher

    SSC Guru

    Points: 98492

    I don't see a more efficient way.  The speed problem may be that it ise UPDATEing every row of the Brs table, since there's no join to it in the query.

    Maybe try this instead:

    UPDATE	B
    SET HrsHKB = M.HRSWERK
    FROM dbo.Brs B
    INNER JOIN MYCTE M
    ON M.IDHuis = B.IDHuis AND
    M.zzpclient_id = B.zzpclient_id

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • JJR333

    Default port

    Points: 1469

    Scott and Steve: thanks, there should have been a join, not sure why it was missed.

    Cheers,

    J.

  • ScottPletcher

    SSC Guru

    Points: 98492

    Great, glad it helped.

    Btw, note that you must ALWAYS use the alias in the UPDATE statement when using a join in an UPDATE.

    --WRONG!!

    UPDATE Brs --<<--WRONG!! MUST be (alias) B

    SET HrsHKB = M.HRSWERK

    FROM dbo.Brs B --<<-- alias assigned to table, as it should be

    INNER JOIN MYCTE M

    ON M.IDHuis = B.IDHuis AND

    M.zzpclient_id = B.zzpclient_id

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • JJR333

    Default port

    Points: 1469

    Yes, thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply