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.
Best Answer
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:
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:
And the results:
And here's a SQL Fiddle for the above.