CODEWITHSUNDEEP
sqlsql-server
j_t_fusion
j_t_fusion

Reputation: 223

update data in table based on data in another table

 table1
-----------------------------
| id (int) |  dt (datetime) |
-----------------------------
|    1     |  12-12-2012    |
|    2     |  13-11-2013    |
|    3     |  23-07-2014    |
|    4     |  13-06-2014    |
-----------------------------


 table2
-----------------------------
| id (int) | dt2 (datetime) |
-----------------------------
|    1     |  12-12-2012    |
|    1     |  13-11-2013    | -> update table1 id=1 with this dt2
|    2     |  23-07-2014    |
|    2     |  13-06-2014    |
|    2     |  12-12-2012    | -> update table1 id=2 with this dt2
|    3     |  13-11-2013    | -> update table1 id=3 with this dt2
|    3     |  23-07-2014    |
|    3     |  13-06-2014    |
|    4     |  23-07-2014    |
|    4     |  13-02-2014    | -> update table1 id=4 with this dt2
-----------------------------

I want to update table1.dt with the corresponding dt2 from table2 based on the id.

However, I do not want to update table1.dt to the largest corresponding datetime value from table2.dt2.

I only want to update dt to the largest corresponding dt2 which is not greater than the current date.

So far what I have gotten is:

update table1
set table1.dt = table2.dt2
from table2
inner join table1  on table1.id = table2.id
where ?table1.id=table2.id and...?

No idea how to modify the sql statement such that it will only update dt to the largest corresponding dt2 which is not greater than the current date.

Hope it isn't too confusing...

Upvotes: 0

Views: 82

Answers (3)

SoupOfStars
SoupOfStars

Reputation: 147

Try this 

UPDATE  table1
SET     [dbo].[Table1].dt = [dbo].[Table2].dt2
FROM    [dbo].[Table2]
where [dbo].[Table2].id = [dbo].[Table1].id and [dbo].[Table2].dt2 > [dbo].[Table1].dt 
and [dbo].[Table2].dt2 <= getdate()

If i'm correcting in what you're trying to achieve you are after the "where" statement that says: where id's are equal and date in Table2 is greater that what you already have in table1 and the date in table2 is less than today? then update.

Upvotes: 1

souplex
souplex

Reputation: 981

Something like this construct would work:

UPDATE  table1
SET     dt = dt2
FROM    ( SELECT    id AS _id ,
                    MAX(dt2) AS dt2
          FROM      ( SELECT    T2.id ,
                                T2.dt2
                      FROM      Table1 T1
                                INNER JOIN Table2 T2 ON T1.id = T2.id
                      WHERE     T2.dt2 < T1.dt
                    ) A
          GROUP BY  id
        ) B
WHERE   id = _id;

Upvotes: 0

ScubaManDan
ScubaManDan

Reputation: 839

You could either using group and aggregates by in your derived table there, or possibly look into cross apply. Here's an example of the former.

update table1 
set table1.dt = table2.dt2 
from table1 join (
       select id,max(dt2) as maxDT
       from table2
       group by id) as derivedTable2 on derivedTable2 .id = table1.id

Upvotes: 0

Related Questions