pl/sql : execute immediate update?

Question

     V_SQL4 := 'UPDATE EMP_TABLE m
     Set m.name = mft.name,
         m.age = mft.age,
         m.dept = mft.dept,
   Where m.id = mft.id and 
         (m.name != mft.name Or
         m.age != mft.age Or
         m.dept != mft.dept )';

DBMS_OUTPUT.PUT_LINE(V_SQL4);

EXECUTE IMMEDIATE V_SQL4;

How and where to declare the temporary table EMP_TMPas mft in the statement?

score 1 · Answer 1 · answered Dec 21 '15 at 07:04

If i look into the requirement i dont see requirment of PL/SQL in this. A better approach woould be using Merge. I have illuistrated an example below. If Dynamic SQL is not hard and bound you can use this too. Let me know if this helps.

MERGE INTO EMP_TABLE m USING EMP_TMP mft 
ON (m.id = mft.id AND (m.name != mft.name OR m.age != mft.age OR m.dept != mft.dept))
WHEN MATCHED THEN
  UPDATE SET 
  m.name = mft.name, 
  m.age = mft.age, 
  m.dept = mft.dept;

score 0 · Answer 2 · edited May 23 '17 at 11:52

This SO post has an answer to a similar question.

In your case, the query would transform as below

V_SQL4 := 'UPDATE EMP_TABLE m
 SET (name, age, dept) = (SELECT mft.name
                                ,mft.age
                                ,mft.dept
                            FROM EMP_TMP mft
                           WHERE m.id = mft.id
                             AND m.name != mft.name Or
                             AND m.age != mft.age Or
                             AND m.dept != mft.dept 
                         )
WHERE EXISTS (
               SELECT 1
                 FROM EMP_TMP mft
                WHERE m.id = mft.id
           )';

pl/sql : execute immediate update?

2 Answers2