Fastest way to update sub-tables in One-to-Many-to-Many relationship

Question

The SQL:

SELECT  [U].[FirstName]
      , [U].[LastName]
      , [U].[Email]
      , [U].[UserId]
      , [AHI].[Id] AS [AdHocId]
      , [R].[AgentId]
      , [R].[RsvpId]
      , [RC].[AgentId] AS [CertAgentId]
      , [RC].[CertId]
FROM    [dbo].[Users] AS U
        LEFT JOIN [dbo].[AdHocIdentity] AS AHI
            ON [AHI].[Email] = [TUI].[Email]
        LEFT JOIN [dbo].[Rsvp] AS R
            ON [R].[AgentId] = [AHI].[Id]
        LEFT JOIN [dbo].[Certificates] AS RC
            ON [RC].[RsvpId] = [R].[RsvpId]
WHERE   [AHI].[Email] IS NOT NULL
        AND [R].[Completed] = 1
        AND ( [AHI].[Email] LIKE '%@%' )
ORDER BY [AHI].[Email]

The output:

    FirstName   LastName    Email                       UserId                                  AdHocId                                 AgentId                                 RsvpId                                  CertAgentId                             CertId
    Babs        Robespiere  babsrobes@yahoo.com         5950B6AB-E2F0-4923-BA45-7AD843D6937F    71283531-90E6-4570-813B-5B1C706030B8    71283531-90E6-4570-813B-5B1C706030B8    03A99511-7FE1-4D8F-B2FB-59E8302FD531    71283531-90E6-4570-813B-5B1C706030B8    FECE9E84-ECE3-4B78-AC2E-A726CCCC8B13
    Babs        Robespiere  babsrobes@yahoo.com         5950B6AB-E2F0-4923-BA45-7AD843D6937F    5950B6AB-E2F0-4923-BA45-7AD843D6937F    5950B6AB-E2F0-4923-BA45-7AD843D6937F    DD29555E-EE06-4724-8FCD-B825AECDCC82    5950B6AB-E2F0-4923-BA45-7AD843D6937F    9E46C992-4CB4-453E-A4D0-BD16587235CA

The Goal:

I want to update the Certificates (RC) .AgentId (CertAgentId), and the Rsvp (r) .AgentId to match the Users (u) .UserId. It would render output like this:

    FirstName   LastName    Email                       UserId                                  AdHocId                                 AgentId                                 RsvpId                                  CertAgentId                             CertId
    Babs        Robespiere  babsrobes@yahoo.com         5950B6AB-E2F0-4923-BA45-7AD843D6937F    5950B6AB-E2F0-4923-BA45-7AD843D6937F    5950B6AB-E2F0-4923-BA45-7AD843D6937F    03A99511-7FE1-4D8F-B2FB-59E8302FD531    5950B6AB-E2F0-4923-BA45-7AD843D6937F    FECE9E84-ECE3-4B78-AC2E-A726CCCC8B13
    Babs        Robespiere  babsrobes@yahoo.com         5950B6AB-E2F0-4923-BA45-7AD843D6937F    5950B6AB-E2F0-4923-BA45-7AD843D6937F    5950B6AB-E2F0-4923-BA45-7AD843D6937F    DD29555E-EE06-4724-8FCD-B825AECDCC82    5950B6AB-E2F0-4923-BA45-7AD843D6937F    9E46C992-4CB4-453E-A4D0-BD16587235CA

My initial thought was a groan as I contemplated a cursor within a cursor, looping in the outer through all of the Users, and in the inner through all of the Rsvps.

But that'll be ugly, and annoying.

And yes, this is a "1-time" run to update newly imported data - so not a scheduled continuing run.

Update The answer:

/* Update RSVPs */
UPDATE  SRR
SET     [AgentId] = [U].[UserId]
FROM    [dbo].[Users] AS U
        LEFT JOIN [dbo].[AdHocIdentity] AS AHI
            ON [AHI].[Email] = [U].[Email]
        LEFT JOIN [dbo].[Rsvp] AS R
            ON [R].[AgentId] = [AHI].[Id]
WHERE   [AHI].[Email] IS NOT NULL
        AND ( [AHI].[Email] LIKE '%@%' )
        AND ([U].[UserId] != [R].[AgentId]);

/* Update Certs */
UPDATE  SRC
SET     [AgentId] = [U].[UserId]
FROM    [dbo].[Users] AS U
        LEFT JOIN [dbo].[AdHocIdentity] AS AHI
            ON [AHI].[Email] = [U].[Email]
        LEFT JOIN [dbo].[Certificates] AS C
            ON [C].[AgentId] = [AHI].[Id]
WHERE   [AHI].[Email] IS NOT NULL
        AND ( [AHI].[Email] LIKE '%@%' )
        AND ([U].[UserId] != [C].[AgentId]);

Is there some reason why this isn't simply a single UPDATE command? That's what it looks like to me. Oh, actually it's two updates since the columns you want to update are in different tables. — Tab Alleman, Dec 18 '14 at 19:44
And I have to update them to the value in a third table. The idea is to maintain the relationships in tables 2 (Rsvp) and 3 (Certificates) while changing their user ids (AgentId in each) to match the user id in table 1 (Users). This is to fix orphaned certificates and rsvps from a previous developer. — PKD, Dec 18 '14 at 19:50
@TabAlleman - I'd love to credit you with the answer, but I don't know how to do that for a comment. Regardless, your comment was actually more helpful than the 'answer' I accepted below. — PKD, Dec 18 '14 at 20:40

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

Such tasks are best accomplished with the use of temporary tables (I usually use local ones). Just load the data you need in one (or several) temporary tables, then do one (or several) updates with joins, and then you're done. Before doing the UPDATEs, you can first run your UPDATEs as SELECTs (using the same WHERE clauses but changing UPDATE to SELECT) just to make sure the UPDATEs will indeed do the right thing.

Local and global temporary tables in SQL Server

SQL update query using joins

edited May 23 '17 at 10:33

Community

1
1

answered Dec 18 '14 at 19:54

peter.petrov

38,363
16
94
159

To any who come here for the answer, I'm tagging this one as the answer, but including the code in my initial post. – PKD Dec 18 '14 at 20:38

Fastest way to update sub-tables in One-to-Many-to-Many relationship

1 Answers1