Re: Multi column update based on another table

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 Received on Tue Jan 15 2008 - 08:08:39 CST