Re: Multi column update based on another table

From: <fitzjarrell_at_cox.net>
Date: Tue, 15 Jan 2008 07:27:05 -0800 (PST)
Message-ID: <c49e3533-6351-4d39-a3e8-3dfe4d3e4abf@s8g2000prg.googlegroups.com>


On Jan 15, 9:04 am, Sashi <small..._at_gmail.com> wrote:
> On Jan 15, 9:08 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > Comments embedded.
> > On Jan 14, 6:53 pm, Sashi <small..._at_gmail.com> wrote:
>
> > > Hi Folks,
> > > I'm writing a simple update statement in Ora 9.2 and am scratching my
> > > head as to why the following syntax is wrong:
>
> > It isn't.  You didn't receive a syntax error message, you received an
> > error message stating your single-row subquery returns more than one
> > row.  Which indicates the 'key' in CITI_DEVICE isn't unique.
>
> > > ---------------------------------------------------------------------------­­---------------------------------
> > > update CITI_CIRCUIT_LBCODE A
> > > Set  (LOOPBACK_ADDR_A, HOST_NAME_A ) =  (SELECT
> > >                                           LOOPBACK_ADDR,
> > >                                           HOST_NAME
> > >                                          FROM
> > >                                          CITI_DEVICE B
> > >                                          WHERE A.DEVICE_NAME_A =
> > > B.DEVICE_ID);
> > > ---------------------------------------------------------------------------­­---------------------------------
>
> > > >[Error] Script lines: 1-7 --------------------------
>
> > >  ORA-01427: single-row subquery returns more than one row
>
> > Meaning that for each row in table A you have multiple rows in table
> > B.  Execute this query to see the problem:
>
> > select device_id, count(*)
> > from citi_device
> > group by device_id;
>
> > You'll notice that most, if not all, device_id's will produce a
> > count(*) greater than 1, and this is the source of your difficulties.
> > You'll need to find a distinct column to join on between the
> > CITI_DEVICE and CITI_CIRCUIT_LBCODE tables, if that's possible.  Or
> > you'll need to find a combination of column values which will return
> > only one row from CITI_DEVICE for each row in CITI_CIRCUIT_LBCODE.
>
> > > Can someone please help out?
> > > Thanks in advance,
> > > Sashi
>
> > David Fitzjarrell
>
> Thanks, all. I thought as much.
>
> I have a question: is it possible to use the distinct clause so that
> the results are distinct only based on one column and not the entire
> record?
> Thanks,
> Sashi- Hide quoted text -
>
> - Show quoted text -

DISTINCT operates on the entire select list, not a single column in it (unless, of course, the select list is but a single column).

You really need to execute the following two queries and see if what is returned by the second is suitable:

--
-- A slight rewrite of your first query
--
SELECT
LOOPBACK_ADDR,
HOST_NAME
FROM
CITI_DEVICE B, CITI_CIRCUIT_LBCODE A
WHERE A.DEVICE_NAME_A = B.DEVICE_ID;

--
-- Return distinct rows
--
SELECT distinct
LOOPBACK_ADDR,
HOST_NAME
FROM
CITI_DEVICE B, CITI_CIRCUIT_LBCODE A
WHERE A.DEVICE_NAME_A = B.DEVICE_ID;

You also need to verify that the second does, indeed, return only one
row for each matched DEVICE_NAME_A value.


David Fitzjarrell
Received on Tue Jan 15 2008 - 09:27:05 CST

Original text of this message