ORACLE Database query - trying to swap row values with multiple tables

Question

I have written a query to swap the row values in the same table based on conditions in multiple tables

UPDATE LV_MESSWERT a1
SET a1.WERT_N = (
    SELECT mw.WERT_N
    FROM   LV_MPRG mp1, LV_PK   pk, LV_PKL pkl, VIEW_DEF3_MESSUNG mess, LV_MESSWERT mw, LV_DLOBJ dl, OSI_OBJ ob, LV_ABBRUCH ab 
    WHERE  (mp1.DEFMES_ID = mess.DEF_MESS_ID AND
          mp1.MPRG_ID = pk.MPRG_ID AND
          pk.PKL_ID = pkl.PKL_ID AND
             pk.PK_ID = mw.PK_ID AND
             mw.DLOBJ_ID = dl.DLOBJ_ID AND
             dl.obj_id = ob.obj_id AND
       mw.MW_ID = a1.MW_ID AND
       mw.SP = a1.sp AND
       ob.KENN = 'DUMMY1' AND
             pk.pk_id = ab.pk_id (+)) AND
            (mp1.POBJ_ID = :pn_pobj AND
             pk.mess_merk = '1'))  where a1.MW_ID in (SELECT mw.MW_ID
    FROM   LV_MPRG mp, LV_PK    pk, LV_PKL pkl, VIEW_DEF3_MESSUNG mess, LV_MESSWERT mw, LV_DLOBJ dl, OSI_OBJ ob, LV_ABBRUCH ab 
    WHERE  (mp.DEFMES_ID = mess.DEF_MESS_ID AND
          mp.MPRG_ID = pk.MPRG_ID AND
          pk.PKL_ID = pkl.PKL_ID AND
             pk.PK_ID = mw.PK_ID AND
             mw.DLOBJ_ID = dl.DLOBJ_ID AND
             dl.obj_id = ob.obj_id AND
       mw.MW_ID = a1.MW_ID AND
       mw.SP = a1.sp AND
       ob.KENN = 'DUMMY3' AND
             pk.pk_id = ab.pk_id (+)) AND
            (mp.POBJ_ID = :pn_pobj AND
             pk.mess_merk = '1'));

the problem here is its updating the values but writing NULL instead of the value. Maybe some one can help me with this query. what am i missing ? so i want all the values in 'WERT_N' in DUMMY_1 to be swapped with WERT_N in DUMMY_3 object.

Answer 1

Your update statement doesn't have a where clause. That means it will update every row in LV_MESSWERT. If your subquery doesn't return a value, the corresponding LV_MESSWERT row will be updated to null.

If you only want to update the rows where the subquery returns a row, you'd need to add a where clause

update LV_MESSWERT a1
   set a1.WERT_N = (<<your subquery>>)
 where exists( <<your subquery>> )