Multi column update based on another table

#21

On Jan 15, 10:27 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net>
wrote:

Quote:

On Jan 15, 9:04 am, Sashi <small... (AT) gmail (DOT) com> wrote:

On Jan 15, 9:08 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

Comments embedded.
On Jan 14, 6:53 pm, Sashi <small... (AT) gmail (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Multi column update based on another table

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Multi column update based on another table - 01-15-2008 , 10:18 AM