Re: Multi column update based on another table

From: <>
Date: Tue, 15 Jan 2008 06:08:39 -0800 (PST)
Message-ID: <>

Comments embedded.
On Jan 14, 6:53 pm, Sashi <> 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.

> ---------------------------------------------------------------------------­---------------------------------
>                                           LOOPBACK_ADDR,
>                                           HOST_NAME
>                                          FROM
>                                          CITI_DEVICE B
>                                          WHERE A.DEVICE_NAME_A =
> ---------------------------------------------------------------------------­---------------------------------
> >[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

Original text of this message