0

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!

OBL
  • 1,347
  • 10
  • 24
  • 45
  • You have two rows in tableb that have exactly the same data. It's not possible to update the date on just one of those. Is a field missing? – Sean Jun 27 '12 at 19:31
  • Also, if you're choosing minimum last auto assigned on date, what criteria to decide between multiple ids with the same minimum date? – Sean Jun 27 '12 at 19:35
  • Also, you haven't mentioned tentative start date, must that be assigned? – Sean Jun 27 '12 at 19:35
  • I have updated the table. Table B was not have duplicates. Tentative start date will be set to getdate(). – OBL Jun 27 '12 at 19:42

1 Answers1

1

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)

Sean
  • 14,359
  • 13
  • 74
  • 124
  • This has to be done dynamically as we run the query. so for each insert we need to get the latest top id from table B order by minimum Last_auto_assigned_on date. – OBL Jun 27 '12 at 20:04
  • Which query? You haven't mentioned any inserts before. – Sean Jun 27 '12 at 20:05
  • Use a while exists loop that checks for nulls. Inside the while code block, get the id you want using top 1 and order by. Update that id to tablea where null and id is the min value in a nested select. Then update table b. that concludes the while code block. – Sean Jun 27 '12 at 20:28
  • Just to clarify: you're getting the id from tableb, and the update of tablea should have read "where Memberid = (select top 1 memberid where tentativeid is null order by memberid)" – Sean Jun 27 '12 at 20:41