Loop Statement to compare two tables

[JReneau35]

I am trying to run a loop statement that compares two tables. We will call them TableA & TableB and pks of LastNameA (tableA) and LastNameB (tableB). TableA is a table that was created some time ago. I want to do a loop that compares the data in TableA to TableB, if it finds that the date for tableA is longer ago than the date for TableB, I want it to update the day for TableA to the date in tableB. Also if the loop finds that there is no information for a last name in TableA as there is in TableB I want it to update TableA to hold the new LastName & date from tableB.



Example:

Table A
LastName (pk) Date
Johnson 5/6/2007
Ralph 9/10/2006
Billings 11/12/2000

Table B
LastName (pk) Date
Johnson 7/7/2007
Ralph 10/11/2005
Billings 12/31/2006
Simmons 7/26/2007

New TableA
LastName (pk) Date
Johnson 7/7/2007
Ralph 9/10/2006
Billings 12/31/2006
Simmons 7/26/2007

[ilearneditonline]

You don't really need to `loop` through the records. You can do a simple update. If you could have new records, then you could so something similar with an insert. But you only mentioned updating the existing records, so that is what I went with.

This should update the records where the date on the comparison table is greater than the current table.

UDPATE TableA
    SET thedate=b.thedate
FROM TableB b INNER JOIN TableA a
ON a.LastName=b.LastName
WHERE b.thedate > a.thedate
GO
 
UDPATE TableB
    SET thedate=a.thedate
FROM TableB b INNER JOIN TableA a
ON a.LastName=b.LastName
WHERE a.thedate > b.thedate
GO

[ilearneditonline]

And you could try something like this for new records.

INSERT TableA(LastName, thedate)
SELECT b.LastName, b.thedate
FROM TableB b LEFT OUTER JOIN TableA
ON b.LastName=a.LastName
WHERE a.LastName ISNULL