Update table A based on tableB

[jayfox]

If I have two tables
Table A:

ID IOL Voced
1234 NULL NULL

Table B:

ID IOL Voced AdmitDate
1234 1 Null 7/1/2005
1234 2 3 7/3/2005
1234 Null NUll 7/5/2005

I would like to update Table A with the most current information from Table B. Here it would be the record from 7/5/2005 but if the values are null then I need to go back and get the last record where they are not null. So in Table A my record should read:

ID IOL Voced Admit Date
1234 2 3 7/5/2005

I can easily do this with another program but how would I accomplish this with T-sql?
I'm guessing some sort of loop. I don't see how a join would work here.

 

[Catadmin]

Your structure for TableA doesn't include an AdmitDate, but your "finished" record indicates that it does. Can you clarify this?

I would almost recommend using a CASE statement in your Set clause, but given that you're pulling from two different result sets (assuming there is an AdmitDate in TableA), I'm not really sure what sort of advice to give you.

You're probably going to have to use a sequence of steps, but they all depend on the answer to the above question.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jayfox]

Sorry I left out admitdate on table should be included

 

[Catadmin]

This probably isn't the best solution, but here goes.

Update TableA
Set AdmitDate = 
   (Select Top 1 AdmitDate from TableB b
    join TableA a 
    on b.ID = a.ID),
IOL = (Select Top 1 IOL from TableB b
       join TableA a 
       on b.ID = a.ID    
       Where IOL IS NOT NULL 
       and AdmitDate = (Select Max(AdmitDate) 
                            from TableB),
Voced = (Select Top 1 Voced from TableB b
       join TableA a 
       on b.ID = a.ID    
       Where Voced IS NOT NULL 
       and AdmitDate = (Select Max(AdmitDate) 
                            from TableB)

I haven't tested this, BTW.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[amrita418]

This query might be slightly faster:

update 		a
set 		a.iol = c.iol,
		a.voced = c.voced,
		a.admitdate = b.admitdate
from 		tableA a, 
		(select 	id, 
				max(admitdate) 'admitdate' 
		 from 		TableB 
		 group by 	id) b
		(select 	id, 
				iol,
				voced
		 from 		TableB i
		 where 		iol is not null and voced is not null 
				and admitdate = (select 	max(admitdate)
						 from 		TableB d
						 where 		i.id = d.id
								and d.iol is not null and d.voced is not null))c
where 		a.id = b.id
		and a.id = c.id

PS: Code not tested.

Regards,
AA

 

[jayfox]

Thanks I'll give it a try

 

[jayfox]

It does not like the second select.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near ')'.
Can you have two selects ina row like that?

 

[maswien]

try this:

create table Va(VID int, IOL int, Voced int, AdmitDate smalldatetime)

create table Vb(VID int, IOL int, Voced int, AdmitDate smalldatetime)

select v1.VID, v1.IOL, v1.Voced, v2.admitdate
from
(select 
(select top 1 VID 
 from Vb 
 where IOL is not null and Voced is not null
  and VID = t0.VID
 order by admitdate desc ) as VID,
(select top 1 IOL 
 from Vb 
 where IOL is not null and Voced is not null
  and VID = t0.VID
 order by admitdate desc ) as IOL,
(select top 1 Voced 
 from Vb 
 where IOL is not null and Voced is not null
  and VID = t0.VID
 order by admitdate desc ) as Voced
from (select distinct VID from VB) t0
) v1 inner join 
(select VID, max(admitdate) as admitdate  from vb group by vid) v2
on v1.VID = v2.VID

 

[amrita418]

I missed a , after one of the selects

create table #TableA (ID int, IOL int, Voced int, AdmitDate datetime)
create table #TableB (ID int, IOL int, voced int, AdmitDate datetime)

insert into #TableA values (1234, NULL, NULL, NULL)
insert into #TableB values (1234, 1, NULL, '7/1/2005')
insert into #TableB values (1234, 2, 3, '7/3/2005')
insert into #TableB values (1234, NULL, NULL, '7/5/2005')

select * from #tableA

update         a
set         a.iol = c.iol,
        a.voced = c.voced,
        a.admitdate = b.admitdate
from         #tableA a, 
        (select     id, 
                max(admitdate) 'admitdate' 
         from         #TableB 
         group by     id) b [COLOR=red],[/color]
        (select     id, 
                iol,
                voced
         from         #TableB i
         where         iol is not null and voced is not null 
                and admitdate = (select     max(admitdate)
                         from         #TableB d
                         where         i.id = d.id
                                and d.iol is not null and d.voced is not null))c
where         a.id = b.id
        and a.id = c.id

select * from #tableA

Regards,
AA