How to update multiple columns in the oracle SQL on ATP fusion DB-CodePudding

I am trying to use the below query, its everytime saying cannot insert null for the second column.

UPDATE TABLE1 SET (COL1,COL2,COL3) = (SELECT COL1,COL2,COL3 FROM TABLE2 where t1.id = t2.id)

CodePudding user response：

Try this:

UPDATE TABLE1 SET (COL1,COL2,COL3) =
     (SELECT COL1,NVL(COL2,0),COL3 FROM TABLE1 T1, TABLE2 T2 where t1.id = t2.id)
     WHERE <YOUR WHERE CONDITION>

CodePudding user response：

I would use a MERGE:

MERGE INTO TABLE1 t1
USING (SELECT id, COL1,COL2,COL3 
         FROM TABLE2) t2
   ON (t1.id = t2.id)
 WHEN MATCHED THEN UPDATE SET t1.COL1 = t2.COL1
                            , t1.COL2 = t2.COL2
                            , t1.COL3 = t2.COL3;