Run a select to make sure it is what you want

SELECT t1.value AS NEWVALUEFROMTABLE1,t2.value AS OLDVALUETABLE2,*
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Update

UPDATE Table2
SET Value = t1.Value
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Also, consider using BEGIN TRAN so you can roll it back if needed, but make sure you COMMIT it when you are satisfied.

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

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.

Hi @vy ，

Welcome to the Microsoft SQL Server Q&A Forum！

Regarding your question, I checked some documents.The conclusion drawn from my own perspective is as follows:

When describing the contents of a table, most people usually display the rows in a specific order. But the table actually represents a collection, and the collection has no order. (Tables with clustered index added are stored in the order of the clustered index columns).

The virtual table(there is no order like a normal table) returned by the inner join operator is as follows：

    id  color   id  color  
    1 NULL 1 red  
    1 NULL 1 blue  

The update statement returns a row from the virtual table to update the t1.color column. The column that satisfies the condition t1.id = c.id has two columns. It is uncertain which column is returned.SQL Server will return the row that happened to be accessed first.Therefore, different results may be produced, but they can all be considered correct. If you want to ensure the certainty of the results, you can choose to include a unique order by list.

Take the select statement as an example:

select top(1) *  
from c   
order by color  

The above select statement specifies a unique order by list (the color field is unique), so the returned result is certain.

The result returned by the following statement is not certain (of course they are all considered correct)：

select top(1) *  
from c   
order by id  
  
select top(1) *  
from c   

In short, SQL Server will return the row that happens to be accessed first, and which row is accessed first is up to the developer.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.