How to update a column in one table by getting values from a column in another table when subquery returns "more than one rows" error.

Toad for Oracle
1

Hi,

I have to update a column of one table by getting the values from a column of another table where the script is throwing the error “ORA-01427: single-row subquery returns more than one row”. I have tried to explain the situation below and the script I am using. Please guide me how to modify my script or write a new script in order to achieve the desired result.

I want to replace values in Table_1.N_Value on the basis of column N_FID where N_Value is ‘21000’, with the values of Table_2.P_Value where Table_1.N_FID = TABLE_2.P_FID

Please let me know if you have any question in order to further understand the situation.

Thanks

Hassan

Table_1

ID N_FID N_Value

1 72 100

2 70 200

3 83 21000

4 83 21000

5 85 21000

6 85 21000

Table_2

ID P_FID P_Value

1 83 1545

2 52 700

3 85 2456

4 47 600

This is the script I am using right now which throws the error.

UPDATE TABLE_1
SET N_VALUE = (SELECT P_VALUE FROM TABLE_2
WHERE P_FID IN (SELECT N_FID FROM TABLE_1
WHERE N_VALUE = ‘21000’))

2

I think you’d be better off using a merge statement than doing an update.

3

You are missing the link between the two tables. You could write it like this:

/* not the best option */

UPDATE TABLE_1

SET N_VALUE =

(SELECT P_VALUE

FROM TABLE_2

WHERE P_FID IN (SELECT N_FID

FROM TABLE_1

WHERE N_VALUE = '21000')

AND p_fid = n_fid)

or this:

UPDATE TABLE_1

SET N_VALUE =

(SELECT P_VALUE

FROM TABLE_2

WHERE 1 = 1

AND p_fid = n_fid)

WHERE N_VALUE = '21000'

On Mon, Dec 12, 2016 at 12:04 PM, arijitkanrar bounce-arijitkanrar@toadworld.com wrote:

RE: How to update a column in one table by getting values from a column in another table when subquery returns "more than one rows" error.

Reply by arijitkanrar
I think you'd be better off using a merge statement than doing an update.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician

Security Gestapo
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

4

Hi Phyllis, I have tried both of your script suggestions but still it returns the same error. Thanks for you help though.

5

Hi Arijit, thanks for your suggestion, it actually worked.

I have used the following statement:

MERGE INTO Table_1 a
USING (SELECT P_FID, P_Value FROM Table_2) b
ON (a.N_FID = b.P_FID)
WHEN MATCHED
THEN
UPDATE SET a.N_Value = b.P_Value
WHERE a.N_Value = ‘21000’

6

Sorry, but this has nothing with Toad.

The best group for SQL based questions (especially rookie one like this one is) is to go to Oracle official forum…

https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql

7

Thanks for your help to solve my problem.