4

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.

  • 2
    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? – Daniel Björk Aug 10 '17 at 16:30
  • 1
    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? – markp Aug 10 '17 at 16:41
  • 1
    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. – Daniel Björk Aug 10 '17 at 16:42
  • 1
    Show the CREATE TABLE statements for both tables, please. – ypercubeᵀᴹ Aug 10 '17 at 16:46
  • 1
    Also about "the order matters": is there other column or columns that define this order in the first table (the one to be updated)? Because from what we see (nulls) no order can be defined. – ypercubeᵀᴹ Aug 10 '17 at 16:48
8

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-29T08:50:00Z
1   2013-01-29T15:28:00Z
2   2013-01-31T11:56:00Z
2   2013-01-31T14:04:00Z
2   2013-03-11T16:08:00Z

And here's a SQL Fiddle for the above.

  • Note that this will give an arbitary matching within each id (as would my solution.). But I dont see any other solution. – Daniel Björk Aug 10 '17 at 17:27
  • 1
    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! :-) – markp Aug 10 '17 at 17:28
  • 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. – Daniel Björk Aug 10 '17 at 17:35
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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