Re: SQL/PSQL Question

Try

update (

select	b.field1
	, b.field2
	, t.data2
	, t.data3
from	bob b
	, import_table t
where	t.import_id = b.import_id
and	b.import_id = 1
) x
set	x.field1 = x.data2
	, x.field2 = x.data3

or

update (

select	b.import_id
	, b.field1
	, b.field2
	, t.data2
	, t.data3
from	bob b
	, import_table t
where	t.import_id = b.import_id
) x
set	x.field1 = x.data2
	, x.field2 = x.data3
where	x.import_id = 1

Martin

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
Received on Wed May 08 2002 - 05:56:35 CDT