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
(t1.col1,t1.col2,t1.col3)=(select distinct case nvl(t2.col4,0)
when '0' then t1.col
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.