Re: Multi column update based on another table

On Jan 15, 10:27 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

That's what I ended up doing.
Thanks,
Sashi Received on Tue Jan 15 2008 - 10:18:11 CST