OraFAQ Forum: Server Administration » Problem with updating a column in a table from another table

Home » RDBMS Server » Server Administration » Problem with updating a column in a table from another table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with updating a column in a table from another table [message #372033]

Fri, 05 January 2001 05:00

Alvin
Messages: 7
Registered: December 2000

Junior Member

Hi!

What im trying to do is transfer to a table column, data from another table column with certain conditions.
This involves multiple rows.

This is the script I made but it returns an error
at line 3.
ORA-01427: single-row subquery returns more than one row

update c_cell_in_msc
set bts_int_id=
(select b.int_id
from c_cell_in_msc a, c_bts b
where conf_name = '<ACTUAL>'
and a.int_id = b.btsm_int_id
and a.cell_id = b.cell_id
and b.conf_name = '<ACTUAL>')
where conf_name = '<ACTUAL>'
and bts_int_id is NULL
/

Thanks!
-Alvin

Report message to a moderator

Re: Problem with updating a column in a table from another table [message #372035 is a reply to message #372033]

Fri, 05 January 2001 07:34

GK
Messages: 22
Registered: January 2001

Junior Member

Hi I have just replaced the "=" in ur query
Try this And see if this is what u wanted

update c_cell_in_msc
set bts_int_id in \* this is what i have changed*\
(select b.int_id
from c_cell_in_msc a, c_bts b
where conf_name = ''
and a.int_id = b.btsm_int_id
and a.cell_id = b.cell_id
and b.conf_name = '')
where conf_name = ''
and bts_int_id is NULL

GK

Report message to a moderator

Re: Problem with updating a column in a table from another table [message #372044 is a reply to message #372033]

Fri, 05 January 2001 12:15

spdevalla
Messages: 7
Registered: December 2000
Location: Rhode Island

Junior Member

The error shows that your subquery returns more than one value. you check your subquery and see that it should return one value only.

I hope this helps you.

Report message to a moderator

Re: Problem with updating a column in a table from another table [message #372048 is a reply to message #372033]

Sat, 06 January 2001 19:33

Todd Lesswing
Messages: 3
Registered: January 2001

Junior Member

Try a PL/SQL program like:

DECLARE

CURSOR C_1 IS
select b.int_id
from c_cell_in_msc a, c_bts b
where conf_name = ''
and a.int_id = b.btsm_int_id
and a.cell_id = b.cell_id
and b.conf_name = '');

BEGIN

FOR C IN C_1 LOOP

update c_cell_in_msc
set bts_int_id = C.b.int_id
where conf_name = ''
and bts_int_id is NULL
END LOOP;
END;
/

You can cut and paste it into SQL Plus or save it to a file at run it with @c:\script.

Report message to a moderator

Re: Problem with updating a column in a table from another table [message #373738 is a reply to message #372033]

Thu, 03 May 2001 14:44

Stephen Moody
Messages: 1
Registered: May 2001

Junior Member

The set commands requires an '=', does it not? I think the only way to do this is through a cursor.

Report message to a moderator

Previous Topic:	Next record
Next Topic:	No of days depts were vacant

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 08:44:45 CST 2024