CODEWITHSUNDEEP
sql-servert-sqlsql-updatetemp-tables
Kate B
Kate B

Reputation: 65

How to update multiple rows in a temp table with multiple values from another table using only one ID common between them?

I am trying to reconcile the IDs in a temp table (top) from another DB table (bottom). Since I only have one ID that's common between the two, I am only getting the top result for all the rows (ReconGlobalRemunerationGrantID) in my temp table. I am aiming to get each of the unique ID and update my temp table as such.

enter image description here

Right now, my update query is simple and I update using the ID common between the 2 tables. Is there a function or another command statement I can use to get the result intended?

update tgrg set ReconGlobalRemunerationGrantID = grg.GlobalRemunerationGrantID from @GlobalRemunerationGrant tgrg
join @GlobalRemuneration tgr on tgr.GlobalRemunerationID = tgrg.GlobalRemunerationID
join DataCore..GlobalRemuneration gr on gr.CompanyID = @CompanyID and gr.FiscalYearID = tgr.FiscalYearID and gr.DirectorDetailID = tgr.DirectorDetailID and tgr.GlobalRoleIDCODE = gr.GlobalRoleID
join DataCore..GlobalRemunerationGrant grg on gr.GlobalRemunerationID = grg.GlobalRemunerationID

Thank you.

Upvotes: 0

Views: 1343

Answers (1)

seanb
seanb

Reputation: 6685

Based on the comment - you have 2 values to match on, not just one? e.g., both GlobalRemunerationID and GlobalRemunerationGrantID?

Here's an example using tables 'temptable' and 't1'

UPDATE temptable
SET    ReconGlobalRemunerationGrantID = t1.GlobalRemunerationGrantID
FROM   temptable
       INNER JOIN t1 ON temptable.GlobalRemunerationID = t1.GlobalRemunerationID
                    AND temptable.GlobalRemunerationGrantID = t1.GlobalRemunerationGrantID

Update below

The below version takes the two data sets

  • Partitions them by GlobalRemunerationID and orders them by ReconGlobalRemunerationGrantID to get the 'row numbers' (rn)
  • Joins them on GlobalRemunerationID and rn to get them in order

Key code is below (with slightly different tables than your full set sorry - matches the data set you gave though).


; WITH tgrg AS
        (SELECT  GlobalRemunerationID, ReconGlobalRemunerationGrantID, 
                 ROW_NUMBER() OVER (PARTITION BY GlobalRemunerationID ORDER BY GlobalRemunerationGrantID) AS rn
            FROM #GlobalRemunerationGrant
        )
    UPDATE  tgrg 
    SET     ReconGlobalRemunerationGrantID = tgr.GlobalRemunerationGrantID 
    FROM    tgrg
            INNER JOIN 
               (SELECT   GlobalRemunerationID, GlobalRemunerationGrantID, 
                         ROW_NUMBER() OVER (PARTITION BY GlobalRemunerationID ORDER BY GlobalRemunerationGrantID) AS rn
                    FROM GlobalRemuneration
                ) AS tgr ON tgrg.GlobalRemunerationID = tgr.GlobalRemunerationID AND tgrg.rn = tgr.rn

A db<>fiddle with the full set is there - note that I changed some of the IDs to demonstrate that it wasn;t using them to match.

Upvotes: 1

Related Questions