UPDATE with JOINS and WHERE clause

  • The following code gives me the correct value for HV2Norm.


    SELECT IDBew, ZPTDoelgroep, ZPTIND, HV2Norm
    FROM
    (
        SELECT B.IDBew, B.ZPTDoelgroep, B.ZPTIND, M.HV2Norm
        FROM     MasterTabelHV2NormNew M JOIN Bewoners B
                    ON  M.ZZPID_DG = B.ZPTind AND
                        M.Jaar = DATEPART(YEAR, B.DateInsert)
        WHERE     B.ZPTInd IN ('1VV','2VV','3VV')

        UNION ALL

        SELECT B.IDBew, B.ZPTDoelgroep, B.ZPTIND, M.HV2Norm
        FROM     MasterTabelHV2NormNew M JOIN Bewoners B
                    ON  M.ZZPID_DG = B.ZPTDoelgroep AND
                        M.Jaar = DATEPART(YEAR, B.DateInsert)
        WHERE     B.ZPTInd NOT IN ('1VV','2VV','3VV')
    ) foo
    ORDER BY foo.ZPTInd

    However this UPDATE code below does not.
    Every row gets the same value for HV2Norm.
    What am I missing?
    Cheers,
    Julian


        -- USE indicatie if ZZP INDICATIE IS 1VV or 2VV or 3VV
         UPDATE Bewoners_STAGING
         SET     Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
         FROM     MasterTabelHV2NormNew M JOIN Bewoners B
                     ON M.ZZPID_DG = B.ZPTind AND
                         M.Jaar = DATEPART(YEAR, B.DateInsert)
         WHERE     B.ZPTInd IN ('1VV','2VV','3VV');
           
         -- USE doelgroep IF ZZP INDICATIE IS NOT 1VV or 2VV or 3VV
         UPDATE Bewoners_STAGING
         SET     Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
         FROM     MasterTabelHV2NormNew M JOIN Bewoners B
                    ON    M.ZZPID_DG = B.ZPTDoelgroep AND       -- <-- use DOELGROEP
                        M.Jaar = DATEPART(YEAR, B.DateInsert)
         WHERE     B.ZPTInd NOT IN ('1VV','2VV','3VV');

  • Is there a 1 to 1 match between both tables?  Check the row count you get from the select vs how many records get updated.

  • You haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value.  This is why certain people on this forum dislike this variant of the UPDATE statement.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Ooops, how obvious.
    Should be the staging table.

  • @drew: thank you Drew for taking the time to look at my code.
    Changing table name 'Bewoners' to 'Bewoners_STAGING' solved my problem.
    Cheers,
    Julian

  • drew.allen - Wednesday, December 13, 2017 3:21 PM

    You haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value.  This is why certain people on this forum dislike this variant of the UPDATE statement.

    Drew

    What would be an alternative to this UPDATE statement?


          UPDATE Bewoners_STAGING
         SET     Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
         FROM    MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
                     ON M.ZZPID_DG = B.ZPTind AND
                         M.Jaar = DATEPART(YEAR, B.DateEdit)
         WHERE   B.ZPTInd IN ('1VV','2VV','3VV');

  • JJR333 - Thursday, December 14, 2017 4:06 AM

    drew.allen - Wednesday, December 13, 2017 3:21 PM

    You haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value.  This is why certain people on this forum dislike this variant of the UPDATE statement.

    Drew

    What would be an alternative to this UPDATE statement?


          UPDATE Bewoners_STAGING
         SET     Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
         FROM    MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
                     ON M.ZZPID_DG = B.ZPTind AND
                         M.Jaar = DATEPART(YEAR, B.DateEdit)
         WHERE   B.ZPTInd IN ('1VV','2VV','3VV');

    If you look at the likes of Oracle, the UPDATEs there have no FROM clause.  Instead, you have to write correlated subqueries in the WHERE clause and it makes things very slow in SQL Server.  Another possibility is to use MERGE to do you updates but that has a raft of it's own problems. 

    Shifting gears a bit, you should get into the habit of using the 2 part naming convention for both performance and data-safety reasons (could have multiple identically named tables in different schemas).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • When joining to do UPDATEs, always update the table alias not the table name itself.  Otherwise you could have the same problem (or other problems).


    UPDATE  B
      SET  B.ZPTHV2NormHrs = M.HV2Norm
      FROM  MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
          ON M.ZZPID_DG = B.ZPTind AND
            M.Jaar = DATEPART(YEAR, B.DateInsert)
      WHERE  B.ZPTInd IN ('1VV','2VV','3VV');

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • JJR333 - Thursday, December 14, 2017 4:06 AM

    drew.allen - Wednesday, December 13, 2017 3:21 PM

    You haven't defined the relationship between the table that you are updating and the source data, so it's as if you're updating every single row in the table with every single value from the results, but SQL is smart enough to only update it with the "last" value.  This is why certain people on this forum dislike this variant of the UPDATE statement.

    Drew

    What would be an alternative to this UPDATE statement?


          UPDATE Bewoners_STAGING
         SET     Bewoners_STAGING.ZPTHV2NormHrs = M.HV2Norm
         FROM    MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
                     ON M.ZZPID_DG = B.ZPTind AND
                         M.Jaar = DATEPART(YEAR, B.DateEdit)
         WHERE   B.ZPTInd IN ('1VV','2VV','3VV');

    I've taken to liking to use CTE to handle joined updates, the update statement itself becomes very straight forward and it's very easy to verify the result set before actually running the update.  So you might have something like this, just switch the comments around if the results look good.

    WITH TEMP_CTE AS ( SELECT B.ZPTHV2NormHrs, M.HV2Norm, M.ZZPID_DG, B.ZPTind, M.Jaar, B.DateEdit FROM  MasterTabelHV2NormNew M JOIN Bewoners_STAGING B
          ON M.ZZPID_DG = B.ZPTind AND
           M.Jaar = DATEPART(YEAR, B.DateEdit)
      WHERE B.ZPTInd IN ('1VV','2VV','3VV');
    )
    SELECT * FROM TEMP_CTE
    --UPDATE TEMP_CTE SET ZPTHV2NormHrs = HV2Norm

  • @scott, @ZZartin: thank you for your suggestions. 🙂

  • My approach using the comments above:


        With myCTE AS
         (
            SELECT    DISTINCT COUNT (*) OVER (PARTITION BY B.ZPTIDHUIS, B.ZPTIDAFD) AS CltAfd,
                      B.ZPTIDHuis AS IDHUIS, B.ZPTIDAfd AS IDAFD
            FROM      Bewoners_STAGING B
            WHERE     B.ZPTCalc <> 'n' AND B.AfdMeetellen <> 'nee'
         )
        
         UPDATE    B
         SET       B.AantalCltn = C.CltAfd
         FROM      Bewoners_STAGING B JOIN myCTE C
                        ON B.ZPTIDHuis = C.IDHuis AND B.ZPTIDAfd = C.IDAfd

Viewing 11 posts - 1 through 10 (of 10 total)

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