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
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'

Open in new window

I am using oracle 9i.
LVL 5
magentoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Combine the queries and look at the counts.

Something like this:
select distinct case nvl(t2.col4,0) when '0' then t1.col else 0 END,
   t2.col4,
   t2.col9,
count(*) mycount
from table t1, table t2 
where t2.col6=t1.col7 and t2.col8=t1.col8)
      and t1.col5 in (1,2) and t1.col10='b.txt'
group by t2.col4, t1.col, t2.col4, t2.col9
having count(*) > 1
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
magentoAuthor Commented:
Line 6 would be  need to remove the bracket ) right?
slightwv (䄆 Netminder) Commented:
>>Line 6 would be  need to remove the bracket ) right?

oops... yes.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

magentoAuthor Commented:
>>>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
slightwv (䄆 Netminder) Commented:
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.
magentoAuthor Commented:
I run the sql twice by changing the file name
Both run i am getting zero records returned.
Please advise.
slightwv (䄆 Netminder) Commented:
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.
magentoAuthor Commented:
Thanks for ur assistance. By adding rownum=1 in the where clause the issue has been solved
slightwv (䄆 Netminder) Commented:
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:
I cant change the sql query , need ur advise how to find the duplicate rows returned in sub query and fix it.
magentoAuthor Commented:
Hi Slight ,

Sorry for late response.

Since I don't have any option I have changed the sql itself.

Thanks
magentoAuthor Commented:
Thanks Slightwv for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.