6I have two table as follows, TABLEA:
MEMBER_ID CLIENT_ID TENTATIVE_ID TENTATIVE_START_DATE
1 65239 26 6/15/2012
2 63693 NULL NULL
3 5549 NULL NULL
4 85452 NULL NULL
5 77898 11 6/15/2012
6 93119 21 6/15/2012
7 7876 26 6/20/2012
8 27572 26 6/21/2012
9 15524 26 6/21/2012
10 39465 8 6/21/2012
11 10143 26 6/23/2012
12 72828 NULL NULL
TABLE B:
TENTATIVE_ID LAST_AUTO_ASSIGNED_ON
26 6/15/2012
11 6/16/2012
21 6/13/2012
27 6/20/2012
28 6/22/2012
29 6/25/2012
8 6/26/2012
21 6/24/2012
Situation is that I need to update the nulls in table A with TENTATIVE_IDs from table B based on minimum LAST_AUTO_ASSIGNED_ON value and every time an id is assigned LAST_AUTO_ASSIGNED_ON is updated with current date time for that id in table B.
In this way we loop though all the tentative ids in table B and assign them to table A.
I am not allowed to use cursor. How could I achieve this?
Thanks!
Update Tablea set tablea.tentative_id = (select top 1 tableb.tentative_id from tableb order by Last_auto_assigned_on, tentative_id), Tentative_start_date = getdate() from tablea where tablea.tentative_id is null
Update tableb set last_auto_assigned_on = getdate() where tentative_id = (select top 1 tableb.tentative_id from tableb order by Last_auto_assigned_on, tentative_id)