Overwrite ID values using UPDATE statement from SELECT in another table SQL Server 2008

I have two tables that have exactly 20 rows in them. I want to overwrite Table2's ID values with the ID values from Table1 so that I can do tests with JOIN queries. How would I go about overwriting the values?

I have found posts instructing how to do an UPDATE using a SELECT statement, however it requires the data to be joined on a column, in my case no columns match.

UPDATE Table2
SET Tab开发者_运维百科le2.ID = Table1.ID
FROM Table1

The query above overwrites all ID columns in Table2 with the value of the first ID column in Table1.

For fun (yes I have a warped sense of humour for a Friday night!), here is a query that does it! - I've declare the tables and the top and only used 6 rows in each table, but you'll get the idea:

--Setup test data
declare @table1 table (ID int, Name varchar(10))
declare @table2 table (ID int, Name varchar(10))

insert @table1
    select ID = 1, Name = 'Item1'
    union select ID = 2, Name = 'Item2'
    union select ID = 3, Name = 'Item3'
    union select ID = 4, Name = 'Item4'
    union select ID = 5, Name = 'Item5'
    union select ID = 6, Name = 'Item6'

insert @table2
    select ID = 11, Name = 'Item11'
    union select ID = 12, Name = 'Item12'
    union select ID = 13, Name = 'Item13'
    union select ID = 14, Name = 'Item14'
    union select ID = 15, Name = 'Item15'
    union select ID = 16, Name = 'Item16'


--Do the update
update t1
    set 
        ID = t2.ID
from
    @table1 t1 --Assign a row number to each row of table 1
    cross apply (select rownum = COUNT(1) from @table1 sub where sub.ID <= t1.ID) x1,
    @table2 t2 --Assign a row number to each row of table 2
    cross apply (select rownum = COUNT(1) from @table2 sub where sub.ID <= t2.ID) x2
where x1.rownum = x2.rownum --Match the row numbers

Update:

Alternative Update command based on suggestion by @sllev:

update t1
    set 
        ID = t2.ID
from
    (select id, rownum  = ROW_NUMBER() OVER(order by ID) from @table1) t1
    join (select id, rownum  = ROW_NUMBER() over (order by id) from @table2) t2
        on t1.rownum = t2.rownum

And another solution, this time using CTE's. Table #First gets the id's from table #Second:

CREATE TABLE #First ( ID INT NOT NULL )
INSERT INTO #First VALUES (1), (2), (3), (4), (5)
CREATE TABLE #Second ( ID INT NOT NULL )
INSERT INTO #Second VALUES (6), (7), (8), (9), (10)
GO

WITH first AS (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM #First)
   , second AS (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM #Second)
UPDATE #First
SET ID = s.ID
FROM second s
JOIN first f ON s.RowNum = f.RowNum
WHERE #First.ID = f.ID

SELECT * FROM #First

DROP TABLE #First;
DROP TABLE #Second;

Since you only have 20 rows in them, I would manually perform the update by typing in your changes. Especially since the records do not correlate to each other as they typically should for an update with a select.

As stated in the comments, if this is a one-off for testing the easiest option for just 20 rows would be to open Table2 in SSMS and type the ID values in.