Sql-server – UPDATE table SET value which SELECT from other table

sql server

I need to update the column on Table_1, and then SET the value which select from Table_2

UPDATE top(3) a 
SET col_1 = col_2_From_Table_2
// Problem here, select col_2 from Table_2 and update into col_1
FROM Table_1

Table_1

ID  | col_1
-------------
11  | null
11  | null
11  | null
11  | null
11  | null

Table_2

col_2
-----
AA
BB
CC

Expected Result of Table_1 (Update top 3 records which select form Table_2)

ID | col_1
----------
11  | AA
11  | BB
11  | CC
11  | null
11  | null

Best Answer

So really these tables aren't related in any way at all. Assuming you want each row in table 2 assigned to a row in table 1, here's one arbitrary way to do it:

;WITH t1 AS 
(
  SELECT ID, col_1, rn = ROW_NUMBER() OVER (ORDER BY col_1)
    FROM dbo.table_1
    WHERE ID = 11 -- guessing here based on sample data
),
t2 AS 
(
  SELECT col_2, rn = ROW_NUMBER() OVER (ORDER BY col_2) 
    FROM dbo.table_2
)
UPDATE t1 
    SET col_1 = t2.col_2
  FROM t1 
  INNER JOIN t2 
    ON t1.rn = t2.rn;

If you have other columns you want to order by, you can add them. If you only want three rows and there are more than three rows in table 2, add:

WHERE t2.rn <= 3;
Related Question