如何从SQL Server中的SELECT进行更新-如果SELECT返回多个项(后续问题2334712)

Question

这是问题的后续内容。

如何在SQL Server中从SELECT进行更新？

如果我使用问题2334712中的查询，我将如何在Table_A中插入"ABC"、"BCD"或"DEF"？

我试过了，但不能在查询中使用ORDER BY子句。

查询结果应该是ABC(如果升序(或DEF(如果降序(，或者基于另一列上的键，比如B3。

UPDATE
Table_A
SET
Table_A.A2 = Table_B.B2
FROM
Table_A
INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE
Table_A.A1 < 10
Table_A:
A1 A2
1       (desired result: can insert 'ABC' or 'DEF' based on my choice)
2 

Table_B:
B1 B2  B3
1  ABC 1
1  BCD 2
1  DEF 3
2  GHI 4

Answer 1

加入一个子查询，该子查询为B1:的每个值标识一条记录

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B2) rn
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN cte b
ON a.A1 = b.B1 AND b.rn = 1
WHERE
a.A1 < 10;

本例假设您希望记录的B1的每个值都具有最低的B2值。但是，您可以将调用更改为ROW_NUMBER，但是您希望使用不同的排序。

Answer 2

您可以在下面使用row_number((进行尝试

WITH t1 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY B1 ORDER BY B3 desc) rownum
FROM Table_B
)
UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN t1 b
ON a.A1 = b.B1 AND rn=1
WHERE
a.A1 < 10;

或者您可以使用相关子查询

UPDATE a
SET A2 = b.B2
FROM Table_A a
INNER JOIN Table_B b
ON a.A1 = b.B1 AND exists (select max(B3) from Table_B c on b.B1 and c.B1)
WHERE
a.A1 < 10;

Answer 3

您可以使用'with'表，类似的东西：

WITH AAA (T_B, ID)
AS
(
SELECT  TOP (SELECT COUNT(*) FROM Table_A)  Table_B.B2 AS RR ,Table_A.A1
FROM Table_A INNER JOIN Table_B
ON Table_A.A1 = Table_B.B1
WHERE Table_A.A1 < 10
ORDER BY 1 DESC-- OR ASC
)
UPDATE Table_A
SET Table_A.A2 = T_B
FROM AAA