Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, RV.

Asked: February 12, 2020 - 2:34 am UTC

Answered by: Chris Saxon - Last updated: February 14, 2020 - 2:39 pm UTC

Category: SQL - Version: 11g R2

Viewed 1000+ times

You Asked

Hello

Pls see the UPDATE statement below ...
I am faced with ORA-01427 when running this
Have also tried "... WHERE EXISTS (SELECT 1 ..." but have not got it right

I need to update the TAXINV and TAXINVDATE columns in TBLTARGET where TAXINV is null,
picking up the corresponding values from the SALEBILLNO and SALEBILLDATE columns in TBLSOURCE
The two Tables are joined by the EXCISEINVNO values, present in both

Hope I've been able to explain ...

I think this is more of a conceptual issue
But have got to get this right as there are more such scenarios which I need to handle

Pls help
Thank you

Notes:
1. Database is 11g R2
2. Using SQL Developer
3. The test case Tables contain only the minimal rows necessary for representation


update tblTarget s1
set (s1.taxinvno, s1.taxinvdate)
=
(
select sb2.salebillno, sb2.salebilldate
from tblTarget se1
left join 
(select * from tblSource
where yearcode='2017-2018' and exciseinvdate>='01-JUL-17') sb2
on se1.exciseinvno=sb2.exciseinvno
where se1.yearcode='2017-2018' and se1.exciseinvdate>='01-JUL-17'
and taxinvno is null
) 
--where exists (???)
;

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



Addendum / 12-Feb-2020
The result of the SELECT below will give you a visual of what I want to update -


select se1.channelcode, se1.exciseinvno, se1.exciseinvdate, taxinvno, taxinvdate, 
sb2.exciseinvno as exciseinvno_sb2 , sb2.exciseinvdate as exciseinvdate_sb2, sb2.salebillno, sb2.salebilldate
from tblTarget se1
left join 
(select * from tblSource
where yearcode='2017-2018' and exciseinvdate>='01-JUL-17') sb2
on se1.exciseinvno=sb2.exciseinvno
where se1.yearcode='2017-2018' and se1.exciseinvdate>='01-JUL-17'
and taxinvno is null


The Columns TAXINVNO, TAXINVDATE, with null values in TBLTARGET,
have to be updated with SALEBILLNO and SALEBILLDATE from TBLSOURCE respectively


with LiveSQL Test Case:

and we said...

A few things:

- To filter the rows to UPDATE, move the where clause from the SET subquery for se1 to the update itself

- There's no need to join tblTarget in the SET subquery; you can access the values you need to from the UPDATE

- I'm not sure you need an EXISTS clause!

In the sample data, moving the where clause identifies the rows to update. You're searching for rows where taxinvno is null.

If there's no matching row in tblSource, then taxinvno remains null after the update. So you don't need to worry about overwriting existing values.

So I believe you can use:

update tblTarget s1
set   (s1.taxinvno, s1.taxinvdate) = (
  select sb2.salebillno, sb2.salebilldate
  from   tblSource  sb2
  where  s1.exciseinvno=sb2.exciseinvno
  and    sb2.yearcode='2017-2018' 
  and    sb2.exciseinvdate>='01-JUL-17'
) 
where s1.yearcode='2017-2018' 
and   s1.exciseinvdate>='01-JUL-17'
and   taxinvno is null


But if you really want EXISTS, use this;

update tblTarget s1
set   (s1.taxinvno, s1.taxinvdate) = (
  select sb2.salebillno, sb2.salebilldate
  from   tblSource  sb2
  where  s1.exciseinvno=sb2.exciseinvno
  and    sb2.yearcode='2017-2018' 
  and    sb2.exciseinvdate>='01-JUL-17'
) 
where s1.yearcode='2017-2018' 
and   s1.exciseinvdate>='01-JUL-17'
and   taxinvno is null
and   exists (
  select * from tblsource sb2
  where s1.exciseinvno = sb2.exciseinvno
  and  sb2.yearcode = '2017-2018'
  and  sb2.exciseinvdate >= '01-JUL-17'
);

and you rated our response

  (2 ratings)

Reviews

UPDATE scenario - facing ORA-01427 - Thank you

February 12, 2020 - 12:06 pm UTC

Reviewer: RV

Thank you for the help!
Allow me to test this out and digest it :-)

UPDATE scenario - facing ORA-01427 - Resolved

February 14, 2020 - 12:36 pm UTC

Reviewer: RV

Just now ran it on the Production data and verified
- worked like a charm!!!
Thanks a ton Chris :-)
Chris Saxon

Followup  

February 14, 2020 - 2:39 pm UTC

Awesome :)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.