Re: Multi column update based on another table

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 Received on Tue Jan 15 2008 - 09:04:42 CST