SQL Question

[Bart Pietercil]

On 30 Jan 2009, at 23:08, Ruslan Zasukhin wrote:

> On 1/30/09 11:58 PM, "Bart Pietercil" <bart.pietercil at cognosis.be>  
> wrote:
>
>> Hi List, Ruslan
>>
>> This query is not accepted by Valentina but explains what I want to  
>> do
>>
>> update a set user_recid = u.recid FROM advita_import a
>> join tbl_users u on a.employee_id = u.hr_db_id;
>>
>> some database accept this syntax but ok, so I considered making a  
>> view
>>
>> But it seems valentina views are only readonly so that won't work
>> either.
>>
>> I know I can do this using subselects but just can't get my head
>> around it. Probably just too late...
>
> Can you explain in english task?
>
>

update the field tbl_a.user_recid with the recid of tbl_user where the  
tbl_a.employee_id = tbl_user.hr_db_id

I could do this by writing a proc using a loop like this

cur1 = select tbl_users.recid, employee_id from tbl_users join tbl_a  
on tbl_users.hr_db_id = tbl_a.employee_id
open cur1
fetch first cur1 into aRecID,anEmployeeID
loop
update tbl_a set user_rec_id = aRecid where employee_id = anEmployeeID
fetch next cur1....
exception when 56... then leave
end loop

but this should work on one update statement no ?

tia

Bart