Home » SQL & PL/SQL » SQL & PL/SQL » Please help with ORA-01427: single-row subquery returns more than one row (Oracle 10.0.2)
Please help with ORA-01427: single-row subquery returns more than one row [message #434622] Thu, 10 December 2009 00:05 Go to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Hi guys

I am writing a query to update a column of a table. When I run the inner query by itself, it runs fine and returns the desired results. However, when the whole query is executed, it returns the error

ORA-01427: single-row subquery returns more than one row

Here's the query I'm writing

update IT_PATCH_091209 a
set a.business_serial = (select (Rn-1) from (
select c.ntn, c.employer_ntn, row_number() over (partition by ntn order by rowid)Rn
from IT_PATCH_091209 c 
where exists (select d.ntn from IT_PATCH_091209 d where d.ntn=c.ntn and d.flag = 'A') 
and exists (select d.employer_ntn from IT_PATCH_091209 d where d.employer_ntn<>c.employer_ntn and d.flag = 'A')
)b )


The above query returns the error
ORA-01427: single-row subquery returns more than one row

When I try to limit the rows by adding rownum=1, then obviously the query executes successfully but it only reads the first row from the inner query and update all the column values to it, which is not desired.

update IT_PATCH_091209 a
set a.business_serial = (select (Rn-1) from (
select c.ntn, c.employer_ntn, row_number() over (partition by ntn order by rowid)Rn
from IT_PATCH_091209 c 
where exists (select d.ntn from IT_PATCH_091209 d where d.ntn=c.ntn and d.flag = 'A') 
and exists (select d.employer_ntn from IT_PATCH_091209 d where d.employer_ntn<>c.employer_ntn and d.flag = 'A')
and rownum=1
)b)



I also tried by matching the inner query and outer query rowids, but what I'm trying is that one condition in the inner query is true while the other is false, so rowids cannot be same.

update IT_PATCH_091209 a
set a.business_serial = (select (Rn-1) from (
select c.ntn, c.employer_ntn, row_number() over (partition by ntn order by rowid)Rn
from IT_PATCH_091209 c 
where exists (select d.ntn from IT_PATCH_091209 d where d.ntn=c.ntn and d.flag = 'A') 
and exists (select d.employer_ntn from IT_PATCH_091209 d where d.employer_ntn<>c.employer_ntn and d.flag = 'A')
)b where a.rowid=b.rowid)


Please help me with this, how can all the corresponding rows in outer query can be updated with appropriate values in the inner query, without returning the ORA-01427: single-row subquery returns more than one row

Thanks.

Hammad


Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434624 is a reply to message #434622] Thu, 10 December 2009 00:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
The behaviour of oracle you described, is quite obvious.

To solve your problem, you have to user co-related sub query.

Can you provide working test case, to help you out?

regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434631 is a reply to message #434622] Thu, 10 December 2009 00:45 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Ok for example I have a table IT_PATCH_091209 which has the following structure.

NTN Employer_NTN Business_Serial
--------------------------------------------------
1234567 0000012 1
1234567 0000013 1
1234567 0000014 1
4567890 0000025 0
4567890 0000026 0
4567890 0000027 0
4567890 0000028 0

After running the query, it should assign a unique Business_Serial to each same NTN with different Employer_NTN. So the table should look like this

NTN Employer_NTN Business_Serial
--------------------------------------------------
1234567 0000012 1
1234567 0000013 2
1234567 0000014 3
4567890 0000025 0
4567890 0000026 1
4567890 0000027 2
4567890 0000028 3

Regards

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434646 is a reply to message #434622] Thu, 10 December 2009 01:56 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
I also tried the following query but it doesn't work as well

update IT_PATCH_091209 a
set a.business_serial = (select (Rn-1) from (
select c.ntn, c.employer_ntn, row_number() over (partition by ntn order by rowid)Rn
from IT_PATCH_091209 c 
where a.ntn = c.ntn 
and a.employer_ntn <> c.employer_ntn
and a.flag = 'A'
)b 


I know I'm missing something - may be a rownum or rowid, but I'm not sure how to solve this one. Please help.

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434649 is a reply to message #434646] Thu, 10 December 2009 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should learn how to properly indent a SQL query, it will help you to find some logical problems.

You can use a tool like http://www.dpriver.com/pp/sqlformat.htm

Regards
Michel
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434656 is a reply to message #434646] Thu, 10 December 2009 02:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        200          1
       100        200          1
       100        200          1
       100        200          1
       100        201          1
       100        201          1
       100        201          1
       100        201          1

8 rows selected.

SQL>ed
Wrote file afiedt.buf

  1  select rowid, col1, col2, col3,
  2  row_number() over(partition by col1, col2 order by col1, col2) update_col
  3* from t9
SQL>r
  1  select rowid, col1, col2, col3,
  2  row_number() over(partition by col1, col2 order by col1, col2) update_col
  3* from t9

ROWID                    COL1       COL2       COL3 UPDATE_COL
------------------ ---------- ---------- ---------- ----------
AAAfJXAAEAAJGDMAAA        100        200          1          1
AAAfJXAAEAAJGDMAAB        100        200          1          2
AAAfJXAAEAAJGDMAAC        100        200          1          3
AAAfJXAAEAAJGDMAAD        100        200          1          4
AAAfJXAAEAAJGDMAAE        100        201          1          1
AAAfJXAAEAAJGDMAAF        100        201          1          2
AAAfJXAAEAAJGDMAAG        100        201          1          3
AAAfJXAAEAAJGDMAAH        100        201          1          4

8 rows selected.


regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434667 is a reply to message #434622] Thu, 10 December 2009 03:19 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Thanks Delna

Actually the situation is

COL1 COL2 COL3
---------- ---------- ----------
100 200 1
100 201 1
100 202 1
100 203 1
101 201 1
101 202 1
101 203 1
101 204 1

i.e. COL2 has different values for a value in COL1, so COL3 should have unique values for these cases. So the table should be

COL1 COL2 COL3
---------- ---------- ----------
100 200 1
100 201 2
100 202 3
100 203 4
101 201 1
101 202 2
101 203 3
101 204 4

Now this query works perfectly well

select (Rn-1) from (
select c.col1, c.col2, row_number() over (partition by c.col1 order by c.col2)Rn
from t9 c 
where exists (select d.col1 from t9 d where d.col1=c.col1) 
and exists (select d.col2 from t9 d where d.col2<>c.col2))


The above query returns the desired results. The problem is when this query becomes an inner query in an update statement.

update t9 a
set col3 = (select (Rn-1) from (
select c.col1, c.col2, row_number() over (partition by c.col1 order by c.col2)Rn
from t9 c 
where exists (select d.col1 from t9 d where d.col1=c.col1) 
and exists (select d.col2 from t9 d where d.col2<>c.col2))) 


This returns the error : ORA-01427: single-row subquery returns more than one row

I also tried the following query and the error remains.

update t9 a
set col3 = (select (Rn-1) from (
select c.col1, c.col2, row_number() over (partition by c.col1 order by c.col2)Rn
from t9 c 
where c.col = a.col1 
and c.col2 <> a.col2)) 


So the main problem is how to UPDATE the value in outer query succesfully.

Thanks.

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434690 is a reply to message #434667] Thu, 10 December 2009 04:41 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        201          1
       100        202          1
       100        203          1
       100        204          1
       101        201          1
       101        202          1
       101        203          1
       101        204          1
       101        205          1

9 rows selected.

SQL>r
  1  update t9 out_tab
  2  set out_tab.col3 =
  3   (select in_tab.rn from (select rowid, row_number() over(partition by col1 order by col2) rn
  4                           from t9) in_tab
  5*   where out_tab.rowid = in_tab.rowid)

9 rows updated.

SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        201          1
       100        202          2
       100        203          3
       100        204          4
       101        201          1
       101        202          2
       101        203          3
       101        204          4
       101        205          5

9 rows selected.


regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434815 is a reply to message #434622] Thu, 10 December 2009 23:26 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Thanks a lot Delna

My problem is almost resolved with your help Smile

There is only one problem left. The code you posted is

update t9 out_tab
set out_tab.col3 =
(select in_tab.rn from (select rowid, row_number() over(partition by col1 order by col2) rn
from t9) in_tab
where out_tab.rowid = in_tab.rowid)


I have to set the col3 incrementing from it's last value against a specific col1. So the query I have to write becomes.

update t9 out_tab
set out_tab.col3 =
(select out_tab.col3+ in_tab.rn-1 from (select rowid, row_number() over(partition by col1 order by col2) rn
from t9) in_tab
where out_tab.rowid = in_tab.rowid)


The above sql works fine where the col3 starts with 0. So it increments the col3 with 0,1,2 ... for one specific col1 with different col2.

Now the problem is that this query does not work where col3 starts with 1, instead of 0. Now I have following rows left in my table.

COL1 COl2 COL3
--------------------
0479268 0000000 1
0479268 0711003 1
2314515 0000000 1
2314515 2567068 1
2363470 0000000 1
2363470 0709507 1


The problem with these rows is that their COL3 starts with 1. So out_tab.col3+ in_tab.rn-1 doesn't work for them.

Any suggestions?

Thanks.

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434817 is a reply to message #434815] Thu, 10 December 2009 23:43 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Use DECODE function.

regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434825 is a reply to message #434622] Fri, 11 December 2009 01:21 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Thanks Delna

The problem is that I have to execute the query 2 times. First time it deals with the col3 starting with 0, the second time it deals with col3 starting with 1.

update t9 out_tab
set out_tab.col3 =
(select out_tab.col3+ in_tab.rn-1 from (select rowid, row_number() over(partition by col1 order by col2) rn
from t9) in_tab
where out_tab.rowid = in_tab.rowid)


Is there a way to deal with all the scenarios while running the query only one time ?

Thanks.

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434829 is a reply to message #434825] Fri, 11 December 2009 02:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Can you show me the exact output you want, with some description?

regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434831 is a reply to message #434825] Fri, 11 December 2009 02:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Is this the output, u want?

SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        201          1
       100        202          1
       100        203          1
       101        201          0
       101        202          0
       101        203          0
       102        201          1
       102        202          1
       102        203          1
       102        204          1

10 rows selected.

SQL>update t9 out_tab
  2  set out_tab.col3 =
  3   (select out_tab.col3 + in_tab.rn - 1 from
  4    (select rowid, row_number() over(partition by col1 order by col2) rn
  5     from t9) in_tab
  6  where out_tab.rowid = in_tab.rowid);

10 rows updated.

SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        201          1
       100        202          2
       100        203          3
       101        201          0
       101        202          1
       101        203          2
       102        201          1
       102        202          2
       102        203          3
       102        204          4

10 rows selected.

regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #434953 is a reply to message #434831] Sat, 12 December 2009 00:20 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
OK Delna I found out what's going on

Actually I am running this query

update t9 out_tab
set out_tab.col3 =
(select out_tab.col3 + in_tab.rn - 1 from
(select rowid, row_number() over(partition by col1 order by col2) rn
from t9) in_tab
where out_tab.rowid = in_tab.rowid);


What this query is doing that it is effecting those rows also which already have different col3 against one particular col1 and different col2. So if we have a table like

COL1 COL2 COL3
---------- ---------- ----------
100 201 1
100 202 1
100 203 1
101 201 0
101 202 0
101 203 0
102 201 1
102 202 1
102 203 1
102 204 0
103 203 1
103 204 2

If we run the above query on this table, what it do is that is also effects the rows with col1 = 103. So the table becomes

COL1 COL2 COL3
---------- ---------- ----------
100 201 1
100 202 2
100 203 3
101 201 0
101 202 1
101 203 2
102 201 1
102 202 2
102 203 3
102 204 4
103 203 1
103 204 1

So now we got a problem with Col1 = 103. Is there a way to only update those rows which have same col, different col2 and same col3 - so col3 can be updated?

Thanks.

Hammad



Re: Please help with ORA-01427: single-row subquery returns more than one row [message #435055 is a reply to message #434953] Mon, 14 December 2009 01:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
My Oracle is working different from you. ./fa/1950/0/

See here,
SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        201          1
       100        202          1
       100        203          1
       101        201          0
       101        202          0
       101        203          0
       102        201          1
       102        202          1
       102        203          1
       102        204          0
       103        203          1
       103        204          1

12 rows selected.

SQL>update t9 out_tab
  2   set out_tab.col3 =
  3   (select out_tab.col3 + in_tab.rn - 1 from
  4   (select rowid, row_number() over(partition by col1 order by col2) rn
  5   from t9) in_tab
  6   where out_tab.rowid = in_tab.rowid);

12 rows updated.

SQL>select * from t9;

      COL1       COL2       COL3
---------- ---------- ----------
       100        201          1
       100        202          2
       100        203          3
       101        201          0
       101        202          1
       101        203          2
       102        201          1
       102        202          2
       102        203          3
       102        204          3
       103        203          1
       103        204          2

12 rows selected.

And in my case, I am getting expected result.

I am confused with, whatever you have described.

Can you clearly specify, what output do you want and with which data?

regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #435203 is a reply to message #435055] Tue, 15 December 2009 03:06 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Ok I have the following table

COL1 COL2 COL3
---------- ---------- ----------
100 201 1
100 202 1
100 203 1
101 201 0
101 202 0
101 203 0
102 201 1
102 202 1
102 203 1
102 204 1
103 203 0
103 204 1

Now the rows with col1 = 103 are OK, means that they do not need to be touched because they already have different COL3 for different COL2 for same COL1.

The query below also effects col1 = 103 and it returns the following output.

SQL>update t9 out_tab
  2   set out_tab.col3 =
  3   (select out_tab.col3 + in_tab.rn - 1 from
  4   (select rowid, row_number() over(partition by col1 order by col2) rn
  5   from t9) in_tab
  6   where out_tab.rowid = in_tab.rowid);


COL1 COL2 COL3
---------- ---------- ----------
100 201 1
100 202 2
100 203 3
101 201 0
101 202 1
101 203 2
102 201 1
102 202 2
102 203 3
102 204 4
103 203 1
103 204 1

So the query also effects Col1 = 103, and sets the Col3 to same value.

Hope it makes some sense.

Thanks

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #435206 is a reply to message #435203] Tue, 15 December 2009 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 09 December 2009 09:14
...
Post a working Test case: create table and insert statements along with the result you want with these data.

Before 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Tue, 15 December 2009 03:13]

Report message to a moderator

Re: Please help with ORA-01427: single-row subquery returns more than one row [message #435208 is a reply to message #435203] Tue, 15 December 2009 03:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What if situation is like,

...
103 202 0
103 203 0
103 204 1
...


regards,
Delna
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #435312 is a reply to message #434622] Tue, 15 December 2009 09:08 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Thanks a lot for your help Delna - the problem is solved now. I changed the query and the following worked !

SQL>update t9 out_tab
  2   set out_tab.col3 =
  3   (select out_tab.col3 + in_tab.rn - 1 from
  4   (select rowid, row_number() over(partition by col1, col3 order by col2) rn
  5   from t9) in_tab
  6   where out_tab.rowid = in_tab.rowid);


I realized that, since we also had to check col3 along with col1 in order to increment the col3 value, (i.e. for same col1 and different col2, there should be different col3) so the rows should not only be partitioned by col1, but also by col3.

Finally it worked for me, thanks again for your time and help Smile

Regards

Hammad
Re: Please help with ORA-01427: single-row subquery returns more than one row [message #435322 is a reply to message #435312] Tue, 15 December 2009 10:25 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solution you'd get 4 or 5 days before if only you would follow the guidelines and post a test case.
Hopefully you got someone to have the patience to follow and help you, this (most likely) might not be the same case in your next post.

Regards
Michel

[Updated on: Tue, 15 December 2009 10:28]

Report message to a moderator

Previous Topic: script to create synonym for all table of dblink
Next Topic: Write image BLOB's to files using PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 16:05:23 CST 2016

Total time taken to generate the page: 0.10401 seconds