Re: SQL/PSQL Question

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 30 Apr 2002 21:30:49 +0200
Message-ID: <itrtcukliaqksqjfe5e1b723dckt7sm6mc@4ax.com>

On 30 Apr 2002 11:19:46 -0700, kshorty_at_phat.com (Kim Goldenberg) wrote:

>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

update bob
set (field1,field2) =

    (select data2, data3

     from import_table 
     where import_id=bob.import_id)

where import_id=1

The other answer is just to be disregarded. It does work, but it is dead slow as every individual record is fetched.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Apr 30 2002 - 14:30:49 CDT