sql - 通过存储过程更新数据

Question

我有下面列出的两个表(表 1 和表 2)。我想在从表 1 获取数据的同时更新表 2 中的列。

表一

+-----------+---------------+---------------------+
+  form_id  +   request_id  +         BatchNo     +
+-----------+---------------+---------------------+
+  5649464  +      562      +     Batch5649464_1  +
+-----------+---------------+---------------------+
+  5649464  +      563      +     Batch5649464_11 +
+-----------+---------------+---------------------+

表2

+------------+---------------+-----------+
+  NumberId  +   ServiceName +    RefId  +
+------------+---------------+-----------+
+  5649464   +      XYZ      +     0     +
+------------+---------------+-----------+
+  5649464   +      XYZ      +     0     +
+------------+---------------+-----------+

在表 1 中，按照上面的列表有两行。默认情况下，我的 RefId 为 0。我必须使用表 1 (request_id) 中的数据更新该列。如您所见，request_id 有两个不同的数据。两个数据(562 和 563)都应该根据我的预期输出进行更新。但现在我正在根据我的初始输出获得结果。 任何人都可以帮助获得我的预期输出吗？

DECLARE @Tempnumbers TABLE
            (
              form_id INT ,
              request_id INT
            );

         ;
        WITH    Result
                  AS ( SELECT   form_id ,
                                CONVERT(BIGINT, ( CONVERT(VARCHAR, request_id) )) AS request_id
                       FROM     [Table1] 
                     )
            INSERT  INTO @Tempnumbers
                    SELECT  form_id, request_id
                    FROM    Result;

        UPDATE  DT
        SET    RefID = request_id
       FROM    Table2 DT 
       INNER JOIN @Tempnumbers TN ON TN.NumberId = DT.form_id;

初始输出

+------------+---------------+-----------+
+  NumberId  +   ServiceName +    RefId  +
+------------+---------------+-----------+
+  5649464   +      XYZ      +     562   +
+------------+---------------+-----------+
+  5649464   +      XYZ      +     562   +
+------------+---------------+-----------+

预期输出

+------------+---------------+-----------+
+  NumberId  +   ServiceName +    RefId  +
+------------+---------------+-----------+
+  5649464   +      XYZ      +     562   +
+------------+---------------+-----------+
+  5649464   +      XYZ      +     563   +
+------------+---------------+-----------+

Answer 1

您可以使用 ROW_NUMBER() 连接表并更新 RefId

;WITH cte1 AS (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY form_id ORDER BY request_id) as rn
    FROM Table1 t1
), cte2 AS (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY NumberId ORDER BY RefId) as rn
    FROM Table2 t2
)

UPDATE c2
SET RefId = c1.request_id
FROM cte1 c1
INNER JOIN cte2 c2
    ON c1.form_id = c2.NumberId AND c1.rn = c2.rn

如果您随后从 Table2 中选择，您将获得:

NumberId    ServiceName RefId
5649464     XYZ         562
5649464     XYZ         563