Re: SQL/PSQL Question

Kim,

Try that:

DECLARE   v_id NUMBER;

  CURSOR my_cur IS

      select data1, data2, data3, data....
         from import_table where import_id = v_id;

BEGIN
  FOR my_rec in my_cur LOOP

     v_id := <whatever way you put your id here>;

    UPDATE bob

       SET field1 = my_rec.data1,
               field2 = my_rec.data2,
               ..............
               ..............
     WHERE import_id = v_id;

EXCEPTION
      WHEN OTHERS THEN
        v_error_code := SQLCODE;
        v_error_message := SUBSTR (SQLERRM, 1, 200);

END;
END LOOP;
COMMIT; END;
/

For more cool scripts and answers check the latest Oracle DBA forums at: http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi

Hope that helps,

Trifon Anguelov
Senior Oracle DBA

http://www.dbaclick.com

"Kim Goldenberg" <kshorty_at_phat.com> wrote in message news:db0be6d6.0204301019.7476ee0a_at_posting.google.com...
> I currently have a query that works, but lacks any elegance. It will
> be added to a stored proc that loops though the import_table and
> creates a new row or updates in table bob. The problem is the update.
>
> update bob
> set field1 = (select data2 from import_table where import_id=1),
> field2 = (select data3 from import_table where import_id=1)
> where import_id = 1;
>
> This would be fine if there were only 2 fields. In some cases there
> are 50 and which would mean 50 subqueries. Ackk.
>
> Could someone please suggest a better way to do this?
>
> Thanks in advance,
> Kim Goldenberg
Received on Tue Apr 30 2002 - 13:38:08 CDT