update with exists in oracle-CodePudding

I have this query :

update rexcms e
   set e.cocnd=(select r.cocnd
                  from rexcms r
                 where NVL(r.marche,'*')=NVL(e.marche,'*')
                   and r.cocrd= e.cocrd
                   and r.cocol=e.cocol
                   and r.cocol='CHBIL'
                   and r.marche='CBOT')
 where e.cocol ='CDVHB' ;

how can use EXISTS to optimize it

CodePudding user response：

Per my understanding, This is optimized code. There is one more syntax as MERGE that you can use to achieve the result -

MERGE INTO rexcms e
USING rexcms r
   ON (NVL(r.marche,'*') = NVL(e.marche,'*')
       and r.cocrd = e.cocrd
       and r.cocol = e.cocol
       and r.cocol = 'CHBIL'
       and r.marche = 'CBOT')
 WHEN MATCHED THEN UPDATE
                      SET e.cocnd = r.cocnd
                    WHERE e.cocol ='CDVHB';

CodePudding user response：

You update the table from rows of the same table. With where e.cocol = 'CDVHB' you only update CDVHB rows. With r.cocol = e.cocol you only update them with data from CDVHB rows. With r.cocol = 'CHBIL' you say that these rows must be CHBIL rows. But a row cannot be a CDVHB and a CHBIL row at the same time, because a row's cocol can only be one or the other.

Your query can hence be reduced to:

update rexcms set cocnd = null where cocol = 'CDVHB';

and there is no need for further optimization.