Re: Help with Cursor Update/delete/insert

syntax of your update is wrong:

select mc_dir set col1=val1, col2=val2, ... where ...;

gcoyle_at_cbs.webramp.net a écrit dans le message <37dff819.16466463_at_news.supernews.com>...
>Hi,
>
>I need to select each record from gctemp and process each record based
>on the trans_code into the mc_dir table.
>
>The trans_codes do the following:
>
>{Peudo Code
>if loc_trancode = 'A' and not found in mc_dir
> insert into mc_dir
>
>if loc_trancode = 'D' then
> delete from mc_dir
>if loc_trancode = 'A' then
> Update mc_dir }
>
>
>I query using:
>
>select lastname,firstname,number_,TRAN_CODE,uid_
> from Mc_Dir MC, gctemp GC
> where
> UPPER(mc.lastname) = UPPER(gc.last_name) and
> UPPER(mc.firstname) = UPPER(gc.first_name) and
> UPPER(mc.number_) = UPPER(gc.extsn) ;
>
>----------------Gives entries in both tables------------
>
>I can's seem to update with the fetch records and i have not started
>to syntax for records not found in MC_dir but found in gctemp do
>insert with fetched records.
>
>Any help would be great See source below,
>Thanks,
>GC
>
>
>declare
>loc_lastname MC_DIR.LASTNAME%TYPE;
>loc_firstname MC_DIR.FIRSTNAME%TYPE;
>loc_number_ MC_DIR.NUMBER_%TYPE;
>loc_trancode GCTEMP.TRAN_CODE%TYPE;
>loc_uid mc_dir.uid_%type;
>
> cursor compare_tables is
> select lastname,firstname,number_,TRAN_CODE,uid_
> from Mc_Dir MC, gctemp GC
> where
> UPPER(mc.lastname) = UPPER(gc.last_name) and
> UPPER(mc.firstname) = UPPER(gc.first_name) and
> UPPER(mc.number_) = UPPER(gc.extsn) ;
>begin
>dbms_output.enable;
> open compare_tables;
> loop
> fetch compare_tables
> into
>loc_lastname,loc_firstname,loc_number_,loc_trancode,loc_uid;
> if compare_tables%notfound
> then
> exit;
> elsif loc_trancode = 'D' then
> delete from mc_dir
> where
> mc_dir.lastname = loc_lastname and
> mc_dir.firstname = loc_firstname and
> mc_dir.number_ = loc_number_;
> commit;
>
>/* the problem statement**************/
>
> elsif loc_trancode = 'A' then
> Update mc_dir
> set = mc_dir.lastname = loc_lastname and
> mc_dir.firstname = loc_firstname and
> mc_dir.number_ = loc_number_;
> commit;
>exit;
>
> end if;
>
> dbms_output.put_line('Processed student: '||loc_lastname );
> end loop;
> close compare_tables;
>end;
>/
>
>
Received on Thu Sep 16 1999 - 02:28:20 CDT