Multi column update based on another table

#1

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:
------------------------------------------------------------------------------------------------------------
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);
------------------------------------------------------------------------------------------------------------

Quote:

[Error] Script lines: 1-7 --------------------------
ORA-01427: single-row subquery returns more than one row

Can someone please help out?
Thanks in advance,
Sashi

#2

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

Quote:

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:
------------------------------------------------------------------------------------------------------------
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

Can someone please help out?
Thanks in advance,
Sashi

The syntax is fine. It looks like DEVICE_ID is not unique in
CITI_DEVICE though.

#3

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

Quote:

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:
------------------------------------------------------------------------------------------------------------
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

Can someone please help out?
Thanks in advance,
Sashi

The syntax is fine. It looks like DEVICE_ID is not unique in
CITI_DEVICE though.

#4

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

Quote:

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:
------------------------------------------------------------------------------------------------------------
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

Can someone please help out?
Thanks in advance,
Sashi

The syntax is fine. It looks like DEVICE_ID is not unique in
CITI_DEVICE though.

#5

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

Quote:

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:
------------------------------------------------------------------------------------------------------------
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

Can someone please help out?
Thanks in advance,
Sashi

The syntax is fine. It looks like DEVICE_ID is not unique in
CITI_DEVICE though.

#6

Comments embedded.
On Jan 14, 6:53*pm, Sashi <small... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

---------------------------------------------------------------------------*---------------------------------
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.

Quote:

Can someone please help out?
Thanks in advance,
Sashi

David Fitzjarrell

#7

Comments embedded.
On Jan 14, 6:53*pm, Sashi <small... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

---------------------------------------------------------------------------*---------------------------------
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.

Quote:

Can someone please help out?
Thanks in advance,
Sashi

David Fitzjarrell

#8

Comments embedded.
On Jan 14, 6:53*pm, Sashi <small... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

---------------------------------------------------------------------------*---------------------------------
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.

Quote:

Can someone please help out?
Thanks in advance,
Sashi

David Fitzjarrell

#9

Comments embedded.
On Jan 14, 6:53*pm, Sashi <small... (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

---------------------------------------------------------------------------*---------------------------------
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.

Quote:

Can someone please help out?
Thanks in advance,
Sashi

David Fitzjarrell

#10

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

Quote:

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