Updating 1 record in Table A with multiple records in Table B

  • Hello.  SQL Server 2012, novice level programmer.  I am trying to UPDATE a master table with records from a daily transaction table using this common SQL construct:
     
    UPDATE
    SET
    Field1 = CASE WHEN s1.Field1 < s2.Field1 THEN s2.Field1 ELSE s1.Field1 END
    Field2 = CASE WHEN s1.Field2 = '' THEN s2.Field2 ELSE s1.Field2 END
    Field3 = CASE WHEN s1.Field3 >= s2.Field3 THEN s2.Field3 ELSE s1.Field3 END
    ...
    from dbo.Table1 s1 INNER JOIN dbo.Table2 s2
    on   s1.CustID = s2.CustID 

    The table have the same columns.  The problem I am having is that there is a one to zero-or-more relationship between Table1 and Table2, and the Table1 records are not showing any update values from any records from Table2 that are the 2nd, 3rd, 4th, etc. record for that CustID.  If there are no records or just 1 record in Table2 for a given CustID in Table1, it works as expected.  Am I missing something or doing something wrong?  Can this approach work in this situation?

  • Please give some test data and expected output.
    Based on current input you are trying to update parent table (Table1) with child table (Table2). Why do you want to use a generic query, if need to update with latest record from table2 then just pick those records using a subquery. If need max value then pick that only and join. just don't join generic and try to update, SQL will update with first record it will match.

  • Here is an example of what I am trying to do.

    My beginning master table:
    RowID    CustID  RecordID   Field1  Field2   Field3
    -------------------------------------------------------
    12655      38      1        K3390            
    66844     165      1                          2885

    My child table
    RowID    CustID  RecordID  Field1  Field2   Field3
    ------------------------------------------------------
      130      38       1      P3477
      178      38       2                         4029
      183      38       3      RS538    4272
      184      38       4               2907      5TRJ
      207     165       1                        6778G
      311     165       2              L3488
      318     165       3     BY23J2              5344

    My desired outcome for the master table:
    RowID   CustID  RecordID  Field1  Field2  Field3
    ----------------------------------------------------
    12655     38       1      K3390    4272    4029
    66844    165       1     BY23J2   L3488    2885

    In this example, the goal is to essentially fill in each blank cell in the master with the first non-blank value from the child table
    .
    In real life, there are a few fields with boolean comparisons between s1 and s2, but I want to take one step at a time.  
    There are 60 attributes with over 1 million child records.  Most fields are VARCHAR but there are some INT as well.

  • Welcome to SSC.  It helps everyone if you post CREATE TABLE and INSERT scripts so people can run them and have at least a mockup of what you're dealing with. That way you get tested answers instead of guesses or no answer... so here's how to post your scripts:
    CREATE TABLE mstr (
        ID INT,
        CustID INT PRIMARY KEY,
        RecordID TINYINT,
        Field1 CHAR(5),
        Field2 CHAR(5),
        Field3 CHAR(5)
    );
    GO
    CREATE TABLE chld (
        RowID INT PRIMARY KEY,
        CustID INT,
        RecordID TINYINT,
        Field1 CHAR(5),
        Field2 CHAR(5),
        Field3 CHAR(5)
    CONSTRAINT fkCustID FOREIGN KEY (CustID) REFERENCES mstr(CustID));
    GO

    INSERT INTO mstr (ID, CustID, RecordID, Field1, Field2, Field3) VALUES
        (12655, 38, 1, 'K3390', NULL, NULL),
        (66844, 165, 1, NULL, NULL, 2885);

    INSERT INTO chld (RowID, CustID, RecordID, Field1, Field2, Field3)
    VALUES (130, 38, 1, 'P3477', NULL, NULL)
        ,(178, 38, 2, NULL, NULL, '4029')
        ,(183, 38, 3, 'RS538', '4272',NULL)
        ,(184,38,4,NULL,'2907','5TRJ')
        ,(207,165,1,NULL,NULL,'6778G')
        ,(311,165,2,NULL,'L3488',NULL)
        ,(318,165,3,'BY232',NULL,'5344');

  • This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

  • pietlinden - Friday, September 8, 2017 11:16 PM

    Welcome to SSC.  It helps everyone if you post CREATE TABLE and INSERT scripts so people can run them and have at least a mockup of what you're dealing with. That way you get tested answers instead of guesses or no answer... so here's how to post your scripts:
    CREATE TABLE mstr (
        ID INT,
        CustID INT PRIMARY KEY,
        RecordID TINYINT,
        Field1 CHAR(5),
        Field2 CHAR(5),
        Field3 CHAR(5)
    );
    GO
    CREATE TABLE chld (
        RowID INT PRIMARY KEY,
        CustID INT,
        RecordID TINYINT,
        Field1 CHAR(5),
        Field2 CHAR(5),
        Field3 CHAR(5)
    CONSTRAINT fkCustID FOREIGN KEY (CustID) REFERENCES mstr(CustID));
    GO

    INSERT INTO mstr (ID, CustID, RecordID, Field1, Field2, Field3) VALUES
        (12655, 38, 1, 'K3390', NULL, NULL),
        (66844, 165, 1, NULL, NULL, 2885);

    INSERT INTO chld (RowID, CustID, RecordID, Field1, Field2, Field3)
    VALUES (130, 38, 1, 'P3477', NULL, NULL)
        ,(178, 38, 2, NULL, NULL, '4029')
        ,(183, 38, 3, 'RS538', '4272',NULL)
        ,(184,38,4,NULL,'2907','5TRJ')
        ,(207,165,1,NULL,NULL,'6778G')
        ,(311,165,2,NULL,'L3488',NULL)
        ,(318,165,3,'BY232',NULL,'5344');

    Thanks for the suggestion, I will do this going forward.

  • m.richardson.home - Saturday, September 9, 2017 2:49 AM

    This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

    I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs?  In my situation, I don't have NULLs I have blanks or spaces (' ').  Will this code still work?

  • jchatton1 - Saturday, September 9, 2017 7:39 AM

    m.richardson.home - Saturday, September 9, 2017 2:49 AM

    This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

    I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs?  In my situation, I don't have NULLs I have blanks or spaces (' ').  Will this code still work?

    Try this instead:
    UPDATE m
    SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), (SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
      Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), (SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
      Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), (SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr AS m

    Please note addition of RTRIM function.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Monday, September 11, 2017 10:21 AM

    jchatton1 - Saturday, September 9, 2017 7:39 AM

    m.richardson.home - Saturday, September 9, 2017 2:49 AM

    This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

    I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs?  In my situation, I don't have NULLs I have blanks or spaces (' ').  Will this code still work?

    Try this instead:
    UPDATE m
    SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), (SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
      Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), (SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
      Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), (SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr AS m

    Please note addition of RTRIM function.

    Thanks, so far this has worked nicely in testing.  I like that COALESCE maintains the order the child values need to be tested.

  • jchatton1 - Wednesday, September 13, 2017 12:32 PM

    sgmunson - Monday, September 11, 2017 10:21 AM

    jchatton1 - Saturday, September 9, 2017 7:39 AM

    m.richardson.home - Saturday, September 9, 2017 2:49 AM

    This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

    I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs?  In my situation, I don't have NULLs I have blanks or spaces (' ').  Will this code still work?

    Try this instead:
    UPDATE m
    SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), (SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
      Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), (SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
      Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), (SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr AS m

    Please note addition of RTRIM function.

    Thanks, so far this has worked nicely in testing.  I like that COALESCE maintains the order the child values need to be tested.

    Glad I could help.   COALESCE always takes the first non-null expression, or if there aren't any non-null values, it returns NULL.   Thus it can be important to understand that the order of the values specified, matters.

    EDIT: I just realized you said the order of the child values.   That is controlled by the SELECT TOP(1) that has an ORDER BY clause.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Would an ON UPDATE CASCADE be a viable option here?

  • sgmunson - Wednesday, September 13, 2017 12:42 PM

    jchatton1 - Wednesday, September 13, 2017 12:32 PM

    sgmunson - Monday, September 11, 2017 10:21 AM

    jchatton1 - Saturday, September 9, 2017 7:39 AM

    m.richardson.home - Saturday, September 9, 2017 2:49 AM

    This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

    I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs?  In my situation, I don't have NULLs I have blanks or spaces (' ').  Will this code still work?

    Try this instead:
    UPDATE m
    SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), (SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
      Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), (SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
      Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), (SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr AS m

    Please note addition of RTRIM function.

    Thanks, so far this has worked nicely in testing.  I like that COALESCE maintains the order the child values need to be tested.

    Glad I could help.   COALESCE always takes the first non-null expression, or if there aren't any non-null values, it returns NULL.   Thus it can be important to understand that the order of the values specified, matters.

    EDIT: I just realized you said the order of the child values.   That is controlled by the SELECT TOP(1) that has an ORDER BY clause.

    One of the issue with this approach is that COALESCE internally breaks into CASE statement, i.e sub query is being evaluated twice. Below is the snapshot of exec plan. Make sure that the no. of rows are less than a million.

    Thanks 🙂

    First solve the problem then write the code !

  • TheCTEGuy - Friday, September 29, 2017 4:59 AM

    sgmunson - Wednesday, September 13, 2017 12:42 PM

    jchatton1 - Wednesday, September 13, 2017 12:32 PM

    sgmunson - Monday, September 11, 2017 10:21 AM

    jchatton1 - Saturday, September 9, 2017 7:39 AM

    m.richardson.home - Saturday, September 9, 2017 2:49 AM

    This should do the trick:-

    UPDATE m
    SET
        Field1 = COALESCE(Field1,(SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
        Field2 = COALESCE(Field2,(SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
        Field3 = COALESCE(Field3,(SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr m

    Column names here are based on the DDL provided earlier - you'll need to change the table names to suit your purposes

    I apologize, I probably wasn't clear enough in my posting, I thought COALESCE only worked with NULLs?  In my situation, I don't have NULLs I have blanks or spaces (' ').  Will this code still work?

    Try this instead:
    UPDATE m
    SET Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), (SELECT TOP 1 Field1 FROM chld c WHERE c.custid = m.custid AND Field1 IS NOT NULL ORDER BY RecordID)),
      Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), (SELECT TOP 1 Field2 FROM chld c WHERE c.custid = m.custid AND Field2 IS NOT NULL ORDER BY RecordID)),
      Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), (SELECT TOP 1 Field3 FROM chld c WHERE c.custid = m.custid AND Field3 IS NOT NULL ORDER BY RecordID))
    FROM mstr AS m

    Please note addition of RTRIM function.

    Thanks, so far this has worked nicely in testing.  I like that COALESCE maintains the order the child values need to be tested.

    Glad I could help.   COALESCE always takes the first non-null expression, or if there aren't any non-null values, it returns NULL.   Thus it can be important to understand that the order of the values specified, matters.

    EDIT: I just realized you said the order of the child values.   That is controlled by the SELECT TOP(1) that has an ORDER BY clause.

    One of the issue with this approach is that COALESCE internally breaks into CASE statement, i.e sub query is being evaluated twice. Below is the snapshot of exec plan. Make sure that the no. of rows are less than a million.

    Thanks 🙂

    The COALESCE function isn't the reason for the multiple scans - it's the fact that a subquery is used for each column being updated, and that subquery does a scan each time.  This code will do only a single scan of the chld table:
    UPDATE m
    SET
         Field1 = COALESCE(NULLIF(RTRIM(Field1), ''), s.MaxField1)
    ,    Field2 = COALESCE(NULLIF(RTRIM(Field2), ''), s.MaxField2)
    ,    Field3 = COALESCE(NULLIF(RTRIM(Field3), ''), s.MaxField3)
    FROM mstr AS m
    JOIN (
        SELECT
             CustID
        ,    MAX(Field1) AS MaxField1
        ,    MAX(Field2) AS MaxField2
        ,    MAX(Field3) AS MaxField3
        FROM chld
        GROUP BY custid
        ) s
    ON m.CustID = s.CustID 

    John

  • Maybe a little bit off track but is there a reason why a COALESCE would be used instead of a CASE statement?

  • You pay your money, you take your choice.  You could easily rewrite as follows - I don't think it would make any difference to performance either way.
    ...Field1 = CASE WHEN Field1 = '' THEN s.MaxField1 ELSE m.Field1 END...

    John

Viewing 15 posts - 1 through 15 (of 18 total)

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