Can this be done in one select?

[Roberto Cano Ramos]

Hi,
I want to do this on just one update

create temporary table roberto
as
(

select
p.ID,
(select id from financial where tipo=2 and id_paciente=p.id_paciente and fecha=p.fecha and isnull(id_cargo) ) as cual
from financial p
where p.tipo=1 );

update financial set id_cargo=(select cual from roberto where id=financial.id );


drop table roberto;

I have tried this without luck:

update financial p set p.id_cargo=(select id from financial where tipo=2 and id_paciente=p.id_paciente and fecha=p.fecha and isnull(id_cargo) )
where p.tipo=1;

Hope to lean something out of this....
Thank

Roberto