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