0

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
4
  • Why do you reference table 2 in code but table 3 in your description? Feb 1, 2015 at 11:29
  • @AaronBertrand I need both Table_1 and Table_2 to find out which column that need to be updated. And then use the column from Table_3 and update into Table_1.
    – My2ndLovE
    Feb 1, 2015 at 11:34
  • I still don't see what table 2 has to do with anything? Does it have rows that aren't in table 1? Is it missing rows from table 1? Feb 1, 2015 at 12:08
  • @AaronBertrand Hmm..the original code need the Table_2 to make it work. Anyway, I have removed it since it is not part of the question.
    – My2ndLovE
    Feb 1, 2015 at 12:20

1 Answer 1

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.