如何使用另一个表中的多个值更新临时表中的多行，仅使用它们之间的一个公共 ID? [英] How to update multiple rows in a temp table with multiple values from another table using only one ID common between them?

问题描述

我正在尝试从另一个数据库表(底部)中协调临时表(顶部)中的 ID.由于我只有一个在两者之间通用的 ID，因此我只能获得临时表中所有行 (ReconGlobalRemunerationGrantID) 的最高结果.我的目标是获取每个唯一 ID 并更新我的临时表.

现在，我的更新查询很简单，我使用两个表之间的公共 ID 进行更新.是否有函数或其他命令语句可以用来获得预期的结果?

从@GlobalRemunerationGrant tgrg 更新 tgrg set ReconGlobalRemunerationGrantID = grg.GlobalRemunerationGrantID在 tgr.GlobalRemunerationID = tgrg.GlobalRemunerationID 上加入 @GlobalRemuneration tgr在 gr.CompanyID = @CompanyID 和 gr.FiscalYearID = tgr.FiscalYearID 和 gr.DirectorDetailID = tgr.DirectorDetailID 和 tgr.GlobalRoleIDCODE = gr.GlobalRoleID 上加入 DataCore..GlobalRemuneration gr在 gr.GlobalRemunerationID = grg.GlobalRemunerationID 上加入 DataCore..GlobalRemunerationGrant grg

谢谢.

解决方案

根据评论 - 您有 2 个要匹配的值，而不仅仅是一个?例如，GlobalRemunerationID 和 GlobalRemunerationGrantID?

这是一个使用表 'temptable' 和 't1' 的示例

UPDATE 临时表SET ReconGlobalRemunerationGrantID = t1.GlobalRemunerationGrantID来自诱惑内部连接 ​​t1 在 temptable.GlobalRemunerationID = t1.GlobalRemunerationIDAND temptable.GlobalRemunerationGrantID = t1.GlobalRemunerationGrantID

---

下方更新

以下版本采用两个数据集

• 按 GlobalRemunerationID 对它们进行分区，并按 ReconGlobalRemunerationGrantID 对它们进行排序以获取行号"(rn)
• 在 GlobalRemunerationID 上加入他们，然后 rn 让他们井井有条

关键代码如下(与您的全套表格略有不同，抱歉 - 与您提供的数据集匹配).

<预><代码>;带有 tgrg AS(SELECT GlobalRemunerationID, ReconGlobalRemunerationGrantID,ROW_NUMBER() OVER (PARTITION BY GlobalRemunerationID ORDER BY GlobalRemunerationGrantID) AS rn来自#GlobalRemunerationGrant)更新 tggSET ReconGlobalRemunerationGrantID = tgr.GlobalRemunerationGrantID从 tgrg内部联接(SELECT GlobalRemunerationID, GlobalRemunerationGrantID,ROW_NUMBER() OVER (PARTITION BY GlobalRemunerationID ORDER BY GlobalRemunerationGrantID) AS rn来自全球薪酬) 作为 tgr ON tgrg.GlobalRemunerationID = tgr.GlobalRemunerationID AND tgrg.rn = tgr.rn

A db<>fiddle 和全套 - 注意我更改了一些 ID 以证明它没有使用它们来匹配.

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.

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.

解决方案

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.

这篇关于如何使用另一个表中的多个值更新临时表中的多行，仅使用它们之间的一个公共 ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！