SQL update multiple rows in destination table with same id but different values from source table

Question

I have two different tables with a common column called id:

Table1
---- -------
id | Date
---- -------
1 null
1 null
2 null
2 null
2 null
2 null
3 null
4 null
4 null
Table2
---- -------
id | Date
---- -------
1 2013年01月29日 08:50:00.000
1 2013年01月29日 15:28:00.000
2 2013年01月31日 11:56:00.000
2 2013年03月11日 16:08:00.000
2 2013年01月31日 14:04:00.000
2 2013年01月31日 14:08:00.000
3 2013年02月28日 23:44:00.000
4 2013年01月31日 14:04:00.000
4 2013年01月31日 14:08:00.000

I need to write an update statement that will basically put the value of the first row with id=1 from Table2 into Table1 with an id=1 and the second row with id=1 from Table2 into Table1 with an id=1 so that Table1 now looks like this

Table1
---- -------
id | Date
---- -------
1 2013年01月29日 08:50:00.000
1 2013年01月29日 15:28:00.000

Currently this SQL statement

UPDATE Table1
 SET Table1.date = T2.date
 FROM 
 Table2 T2
 INNER JOIN Table1 T1
 ON T1.date = T2.date

is making Table1 look like this, where it applies the first date it finds for that id to all the rows with matching ids in the destination table

Table2
---- -------
id | Date
---- -------
1 2013年01月29日 08:50:00.000
1 2013年01月29日 08:50:00.000

Is there a way to udpate Table1 to look like this below:

Table1
---- -------
id | Date
---- -------
1 2013年01月29日 08:50:00.000
1 2013年01月29日 15:28:00.000
2 2013年01月31日 11:56:00.000
2 2013年03月11日 16:08:00.000
2 2013年01月31日 14:04:00.000
2 2013年01月31日 14:08:00.000
3 2013年02月28日 23:44:00.000
4 2013年01月31日 14:04:00.000
4 2013年01月31日 14:08:00.000

Is there a way to do this in a SQL statement? I don't want to use a cursor preferably.

There are other columns in both the tables and not all id values in Table1 might be there in Table2 and vice versa. Those other rows and columns I don't want to touch or alter.

• Daniel Björk: Are there exacly same row count in both tables? Does it mather what row a row is match with in the other table aslong as it has same id? What RDBMS are you using?
• markp-fuso: what happens if Table2 only has 1 row for id=1? do you leave one of the Table1.id=1 rows with Date=NULL, or do you set both Table1 rows with the same Date value from Table2?
• Daniel Björk: I think you might have a problem then if you dont have anything that can help you sort the tables in same order as you want to match the tables. The thing is that you can not garantuee order of tables when fetching data if not using ORDER BY.
• ypercubeᵀᴹ: Show the CREATE TABLE statements for both tables, please.
• ypercubeᵀᴹ: So the table has no primary key or unique constraint?

Answer 1

Couldn't find a SQL Server 2008 fiddle engine so I had to opt for a SQL Server 2014 ... so not sure if the following will work in SQL Server 2008, but fwiw ...

Setup some sample data:

create table Table1(id int, Date datetime null);
create table Table2(id int, Date datetime);
insert Table1 values (1,null)
insert Table1 values (1,null)
insert Table1 values (2,null)
insert Table1 values (2,null)
insert Table1 values (2,null);
insert Table2 values (1,'2013-01-29 08:50:00.000')
insert Table2 values (1,'2013-01-29 15:28:00.000')
insert Table2 values (2,'2013-01-31 11:56:00.000')
insert Table2 values (2,'2013-03-11 16:08:00.000')
insert Table2 values (2,'2013-01-31 14:04:00.000');

Keeping in mind that we haven't been provided (yet) with any means to determine which rows to match between Table1 and Table2 for a given id value, I'll just let row_number() generate a 'matching' rowid.

And then we'll make use of SQL Server's ability to update Table1 via a derived table definition:

update T1 
set T1.Date=T2.Date
from (select row_number() over(partition by id order by Date) as rowid,
 id,
 Date
 from Table1 
 where Date is NULL) T1
join (select row_number() over(partition by id order by Date) as rowid,
 id,
 Date
 from Table2) T2
on T1.id = T2.id
and T1.rowid = T2.rowid;

And the results:

select * from Table1;
id Date
--- --------------------
1 2013年01月29日T08:50:00Z
1 2013年01月29日T15:28:00Z
2 2013年01月31日T11:56:00Z
2 2013年01月31日T14:04:00Z
2 2013年03月11日T16:08:00Z

And here's a SQL Fiddle for the above.

• Daniel Björk: Note that this will give an arbitary matching within each id (as would my solution.). But I dont see any other solution.
• markp-fuso: correct, we've both noted as much in our answers; I'm guessing there may be some other fields in the tables that could be used for ordering, but until/unless the OP provides such data ... we get to roll the dice! :-)
• Daniel Björk: Yeah, for his sake we hope that there is something to order by and in that case we just need to update the order by statement in the row_number() clause. Otherwise its not possible to solve.

Answer 2

You stated that the order of the matching matters but it seems like you don't have anything to ORDER BY in table 1 to create a guaranteed order to match the other table and there is no way in SQL Server to order the rows after insertion date, because information about that is not stored. With this in mind it’s not possible to do a matching with the result you want. There is a solution to update the rows with an arbitrary match within each id. If that would be good enough.

UPDATE t 
SET t.[date] = tt.[date] 
FROM (SELECT *, 
 Row_number() 
 OVER ( 
 partition BY id 
 ORDER BY [date]) AS rno 
 FROM Table1) AS t 
 INNER JOIN (SELECT *, 
 Row_number() 
 OVER ( 
 partition BY id 
 ORDER BY [date]) AS rno 
 FROM Table2) AS tt 
 ON t.id = tt.id 
 AND t.rno = tt.rno 

This solution will match all rows individually but can't guarantee the order.

DB Fiddle