How do I UPDATE from a SELECT in SQL Server - if the select returns multiple items (Follow up question 2334712)

Question

This is a follow up of the question.

How do I UPDATE from a SELECT in SQL Server?

If I use the query in question 2334712, How would I insert 'ABC', 'BCD' or 'DEF' in the Table_A?

I tried but I can't use the ORDER BY clause in query.

Query result should be ABC (If ascending order), or DEF (If descending order), or based on a key on another column, say 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

there are three values in tableB for B1=1. In your question you said "based on my choice" - could you please explain what is your choice — Fahmi, Nov 12 '18 at 09:59
The choice can be highest value in B3, lowest value in B3 or specific value in B3 (e.g. B3 = 2) — chinghp, Nov 12 '18 at 10:06

score 2 · Answer 1 · answered Nov 12 '18 at 10:00

Join to a subquery which identifies a single record for each value of 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;

This example assumes you want the record having the lowest B2 value for each value of B1. But, you may alter the call to ROW_NUMBER however you want to use a different ordering.

score 0 · Answer 2 · answered Nov 12 '18 at 10:15

You can try below using 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;

OR you can use correlated subquery

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;

score 0 · Answer 3 · answered Nov 12 '18 at 10:34

You'll can use 'with' table, Something like that:

    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

How do I UPDATE from a SELECT in SQL Server - if the select returns multiple items (Follow up question 2334712)

3 Answers3