How to update another table with the most recent data in SQL?

Question

I'm trying to update Table1 in DB1 with data from Table2 in DB2. I can connect and get the data from DB2 Table2 into DB1 Table1, but the issue I'm having is getting the MOST RECENT data from DB2 Table2.

I'm looking at 3 fields in DB2: f1, f2, & f3. f1 contains duplicates (and is where I'm matching from DB1 Table1) and f3 is a date field and I want to grab the most recent date to update DB1 Table1. Below is some of the code I've been using:

Update Table1
Set f2 = c.f2, 
    f3 = convert(varchar, c.f3, 101) 
From Table1 b 
    inner join Server.DB.dbo.Table2 c on b.f1 = c.f1
Where b.f1 = c.f1

Sample Data:

c.f1    c.f2      c.f3
8456    RS47354      06/30/2009
8456    M101021      10/31/2009 (want this one)
7840    5574      NULL
7840    RH013057     06/30/2010 (want this one)
7650    RS48100      06/30/2007
7650    RS49010      06/30/2009 (want this one)

b.f1        b.f2         b.f3
8456        Null         Null
7840        Null         Null
7650        Null         Null

Eventually, this will be set inside an SSIS package.

Any and all help appreciated!

-JFV

Answer 1

and (b.f3 > c.f3 OR b.f3 is null)

In your where clause, of course.

If I'm reading your question right, anyway...

Answer 2

I'm not sure if this is the fasted code in the world, it obviously depends on how close the two servers are and how much data you have in each table.

UPDATE Table1
SET 
    f2 = T2.f2, 
    f3 = convert(varchar, T2.f3, 101) 
FROM 
    Table1 T1
INNER JOIN 
    Server.DB.dbo.Table2 T2
ON 
    T1.f1 = T2.f1
WHERE 
    T2.f3 = (SELECT MAX(f3) FROM Server.DB.dbo.Table2 WHERE f1 = T1.f1)

An alternative (if you have that much control) is to create a trigger on Table2 which puts the latest version into a temporary table whenever it is updated.

Update: Corrected the code.

Answer 3

UPDATE
     T1
SET
     f2 = T2.f2,
     f3 = T2.f3  -- If it's a date, save it as a date, not a VARCHAR
FROM
     dbo.Table1 T1
INNER JOIN Server.db.dbo.Table2 T2 ON
     T2.f1 = T1.f1
LEFT OUTER JOIN Server.db.dbo.Table2 T2_later ON
     T2_later.f1 = T2.f1 AND
     T2_later.f3 > T2.f3
WHERE
     T2_later.f1 IS NULL

This may have some performance problems doing it across servers if Table2 is large. It might be better to create a view in that database and use that for the updates:

CREATE VIEW dbo.T2_Latest
AS
     SELECT
          T2.f1,
          T2.f2,
          T2.f3
     FROM
          dbo.Table2 T2
     LEFT OUTER JOIN dbo.Table2 T2_later ON
          T2_later.f1 = T2.f1 AND
          T2_later.f3 > T2.f3
     WHERE
          T2_later.f1 IS NULL

Then you just need to join on f1 (you don't need that criteria in both the INNER JOIN and the WHERE clause by the way). The view will filter out the earlier rows BEFORE it needs to compare them across servers.

In SSIS there are other solutions, using the Merge component, Lookup component, or Join component which will probably work better.