Home » SQL & PL/SQL » SQL & PL/SQL » Query (Single row sub query returns more than one row)
Query [message #412389] Thu, 09 July 2009 02:12 Go to next message
preethi 12$
Messages: 9
Registered: January 2008
Junior Member

I am using the below update statement inside a cursor.
But getting the error "Single row sub query returns more than one row" as multiple records are returned by the select statement.

As I am already using a cursor('j' is the cursor variable), not sure how to update the record one after the other.
Please help!

UPDATE pay_plan_2 a
SET frst_pol_eff_dt =
(SELECT bsp_rnw_effdt
FROM pay_plan_1 a
WHERE a.mco = j.pms_mco_cd
AND a.mode_cd = j.pms_mode_cd
AND a.pol_issue_cd = j.pol_issue_cd
AND a.st = j.st_cd
AND a.paybycd = j.pay_by_cd
AND a.tier = j.rad_uw_tier_grp_cd
AND a.lob_cd = j.lob_cd
AND a.pco = j.pms_pco_cd
AND a.pay_plan_cd = j.pay_plan_cd);
Re: Query [message #412394 is a reply to message #412389] Thu, 09 July 2009 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65082
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 08 July 2009 14:41
Michel Cadot wrote on Wed, 08 July 2009 09:47
Michel Cadot wrote on Tue, 07 July 2009 09:29
Are you sure you can't do it in a simple SQL statement?

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Also always post your Oracle version (4 decimals).

Before posting any code, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.


Re: Query [message #412402 is a reply to message #412389] Thu, 09 July 2009 03:20 Go to previous message
Messages: 1844
Registered: November 2006
Senior Member
The error message is quite descriptive - there are multiple rows which satisfy the conditions in the WHERE clause. This is more logical problem as one column in one row may contain only one value. In fact, which of that values shall be set to the column?

Either choose the condition identifying at most one row (e.g. using primary or unique key columns), or (if you do not care which value will be set), add "ROWNUM = 1" condition.
By the way, are you aware, that the column is updated to NULL when the subquery returns no row?
And, as UPDATE contains no WHERE clause, that it updates all rows in the table (which is quite unusual inside cursor loop)?

[Edit: Added the last remark]

[Updated on: Thu, 09 July 2009 03:32]

Report message to a moderator

Previous Topic: Compare records using cursor
Next Topic: Query to find table's with primary key
Goto Forum:

Current Time: Fri Jul 21 08:06:26 CDT 2017

Total time taken to generate the page: 0.05640 seconds