magento
asked on
select sql needed for update issue
Hi,
I will receive 2 files say a.txt and b.txt .
For both the files the below update sql run.The file name will be a variable in where clause at the last as u see below.
But when it runs for a.txt it gives 20 rows updated , when it run for b.txt i am getting the below error.
ORA-01427: single-row subquery returns more than one row.
I cant change the sql query , need ur advise how to find the duplicate rows returned in sub query and fix it.
Hope you can give me a select query to check where the duplicate is , so i can run the sql for both a.txt and b.txt and find the duplicate
I will receive 2 files say a.txt and b.txt .
For both the files the below update sql run.The file name will be a variable in where clause at the last as u see below.
But when it runs for a.txt it gives 20 rows updated , when it run for b.txt i am getting the below error.
ORA-01427: single-row subquery returns more than one row.
I cant change the sql query , need ur advise how to find the duplicate rows returned in sub query and fix it.
Hope you can give me a select query to check where the duplicate is , so i can run the sql for both a.txt and b.txt and find the duplicate
update table t1
set
(t1.col1,t1.col2,t1.col3)=(select distinct case nvl(t2.col4,0)
when '0' then t1.col
else 0
END,
t2.col4,t2.col9
from table t2 where t2.col6=t1.col7 and t2.col8=t1.col8)
where t1.col5 in (1,2) and t1.col10='a.txt'
I am using oracle 9i.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Line 6 would be need to remove the bracket ) right?
oops... yes.
oops... yes.
ASKER
>>>group by t2.col4, t1.col, t2.col4, t2.col9
t1.col ? please advise which column
Also do we need to use t2.col4 twice?
Thanks
t1.col ? please advise which column
Also do we need to use t2.col4 twice?
Thanks
I copied your original code and did some copy/paste/edit myself. Your code had "when '0' then t1.col
", so that means mine had t1.col.
I just cobbled together an 'example' based on what you posted. I cannot do a syntax check since I don't have your tables.
Look at the concept and adapt it to your tables.
Looks like it should have been t1.col1. No, you don't need col4 twice.
", so that means mine had t1.col.
I just cobbled together an 'example' based on what you posted. I cannot do a syntax check since I don't have your tables.
Look at the concept and adapt it to your tables.
Looks like it should have been t1.col1. No, you don't need col4 twice.
ASKER
I run the sql twice by changing the file name
Both run i am getting zero records returned.
Please advise.
Both run i am getting zero records returned.
Please advise.
I might have missed a column or two.
Do you understand what I was attempting?
Something in your select statement is causing more than one row.
By combining the select portion with the table and where in the update portion, you should be able to figure out where the duplicates are.
I cannot provide a 100% accurate copy/paste working solution. I would need your tables and test data.
Do you understand what I was attempting?
Something in your select statement is causing more than one row.
By combining the select portion with the table and where in the update portion, you should be able to figure out where the duplicates are.
I cannot provide a 100% accurate copy/paste working solution. I would need your tables and test data.
ASKER
Thanks for ur assistance. By adding rownum=1 in the where clause the issue has been solved
That only masks the problem and may not return the correct rows data.
Also, changing the where clause wasn't an option for us.
The question asked was:
Also, changing the where clause wasn't an option for us.
The question asked was:
I cant change the sql query , need ur advise how to find the duplicate rows returned in sub query and fix it.
ASKER
Hi Slight ,
Sorry for late response.
Since I don't have any option I have changed the sql itself.
Thanks
Sorry for late response.
Since I don't have any option I have changed the sql itself.
Thanks
ASKER
Thanks Slightwv for your help
ASKER