# 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. **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [December 12, 2016, 8:55am UTC](https://forums.toadworld.com/t/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/38794 "2016-12-12T08:55:50Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![hassangeo](https://avatars.discourse-cdn.com/v4/letter/h/8c91f0/32.png) [@hassangeo](https://forums.toadworld.com/u/hassangeo)\ **Post date:** [December 12, 2016, 8:55am UTC](https://forums.toadworld.com/t/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/38794/1 "2016-12-12T08:55:50Z")
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’)) ---
**Author:** ![arijitkanrar](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/arijitkanrar/32/335_2.png) [@arijitkanrar](https://forums.toadworld.com/u/arijitkanrar)\ **Post date:** [December 12, 2016, 9:02am UTC](https://forums.toadworld.com/t/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/38794/2 "2016-12-12T09:02:09Z")
I think you’d be better off using a merge statement than doing an update. ---
**Author:** ![phyllis.helton\_1150](https://avatars.discourse-cdn.com/v4/letter/p/ac8455/32.png) [@phyllis.helton\_1150](https://forums.toadworld.com/u/phyllis.helton_1150)\ **Post date:** [December 12, 2016, 10:51am UTC](https://forums.toadworld.com/t/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/38794/3 "2016-12-12T10:51:06Z")
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](mailto: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.](http://www.toadworld.com/products/toad-for-oracle/f/10/p/31869/63926#63926)** > 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](http://www.toadworld.com/products/toad-for-oracle/f/10/t/31869?subscribe=false) on this subject. > > Or [Unsubscribe from Toad for Oracle Forum](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether. > > Toad for Oracle - Discussion Forum > [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/31869/63926?AbuseContentId=96016449-67c1-4c36-acc3-40a5b8bfe8fa&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse. -- **Phyllis Helton** Data Magician Security Gestapo Digital Products & Strategies, Cru | Data Sciences & Analytics Office ☎ 407-515-4452 [phyllis.helton@cru.org](mailto:phyllis.helton@cru.org) ![](https://global.discourse-cdn.com/quest/original/3X/9/4/94bf0c4f5e9dec7932932b0cb0b94169a403b1fd.png) ---
**Author:** ![hassangeo](https://avatars.discourse-cdn.com/v4/letter/h/8c91f0/32.png) [@hassangeo](https://forums.toadworld.com/u/hassangeo)\ **Post date:** [December 15, 2016, 12:32pm UTC](https://forums.toadworld.com/t/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/38794/4 "2016-12-15T12:32:35Z")
Hi Phyllis, I have tried both of your script suggestions but still it returns the same error. Thanks for you help though. ---
**Author:** ![hassangeo](https://avatars.discourse-cdn.com/v4/letter/h/8c91f0/32.png) [@hassangeo](https://forums.toadworld.com/u/hassangeo)\ **Post date:** [December 15, 2016, 12:33pm UTC](https://forums.toadworld.com/t/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/38794/5 "2016-12-15T12:33:56Z")
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’ ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [December 16, 2016, 3:57am UTC](https://forums.toadworld.com/t/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/38794/6 "2016-12-16T03:57:13Z")
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](https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql) ---
**Author:** ![hassangeo](https://avatars.discourse-cdn.com/v4/letter/h/8c91f0/32.png) [@hassangeo](https://forums.toadworld.com/u/hassangeo)\ **Post date:** [December 16, 2016, 6:46am UTC](https://forums.toadworld.com/t/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/38794/7 "2016-12-16T06:46:15Z")
Thanks for your help to solve my problem.