Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Multi table update query problem

Re: Multi table update query problem

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sun, 13 May 2001 00:22:26 -0700
Message-ID: <3AFE3632.913A3112@exesolutions.com>

Jonathan Lord wrote:

> I am in the process of a data migration and am trying to convert from
> an old to a new schema. Various items in the old schema like state
> codes, nation codes, etc. were character values that now have been
> normalized into support tables with a numeric primary key. I am
> working on setting the new values of the normalized keys into a
> temporary table so that I can build tuples in the new schema to get
> the data moved. When I try to update the temporary table with the
> following sql, oracle chokes and says that my sub query is returning
> more than one row.
>
> update ardata.addrtemp set ardata.addrtemp.druidnation =
> ( select lunation.druidnation
> from ardata.addrtemp , lunation
> where addrtemp.country = lunation.nationpostalabbr );
>
> The attempt here is to link the temp table to the new nation table
> based on a character string to get the id of that tuple in the nation
> table that contains the character string as an abbreviation for the
> nation. Once that numeric value is returned, I want it to update the
> corresponding value in the temp table.
>
> This should be trivial, but I have searched high and low through the
> doc for assistance, and didn't find an example even remotely
> resembling this activity.
>
> advTHANKSance
>
> Jonathan

Look at using DISTINCT or GROUP BY with a function to force a single row return

Daniel A. Morgan Received on Sun May 13 2001 - 02:22:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US