CODEWITHSUNDEEP
sqldb2
smat88dd
smat88dd

Reputation: 2388

How to change this db2 merge query?

can someone help me? i am trying to convert the following merge into another query and i am allowed only to use insert and update once:

MERGE INTO MYEMPLOYEE ME USING EMPLOYEE E ON ME.EMPNO = E.EMPNO 
WHEN MATCHED THEN 
UPDATE SET ME.SALARY = CASE WHEN ME.SALARY > E.SALARY THEN ME.SALARY ELSE E.SALARY END 
WHEN NOT MATCHED THEN 
INSERT VALUES(E.EMPNO, E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT, E.PHONENO, E.HIREDATE, E.JOB, E.EDLEVEL, E.SEX, E.BIRTHDATE, E.SALARY, E.BONUS, E.COMM);

How can I achieve this? the above merge copies the data if not exists, and if it exists it checks for the salary and selects the higher one and copies that one. how can I achieve the same thing by only using one insert and one update? Can someone give me hints, please?

Thanks in advance :)

Upvotes: 0

Views: 480

Answers (2)

smat88dd
smat88dd

Reputation: 2388

After trying I noticed that the INSERT code should actually be like this:
1) Don't use the Name ME twice
2) Select the necessary columns because after the join there are twice the column count

INSERT INTO MYEMPLOYEE (
    SELECT E.EMPNO, E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT, E.PHONENO, E.HIREDATE, E.JOB, E.EDLEVEL, E.SEX, E.BIRTHDATE, E.SALARY, E.BONUS, E.COMM 
    FROM EMPLOYEE E LEFT OUTER JOIN MYEMPLOYEE ME ON E.EMPNO = ME.EMPNO WHERE ME.EMPNO IS NULL
);

Upvotes: 0

GeekyDaddy
GeekyDaddy

Reputation: 384

The purpose of the MERGE command is suppose to take into account multiple actions of UPDATE, INSERT, DELETE. MERGE statement explained

If you cannot/unable to use MERGE, then you have to resort to doing each request individually. 

UPDATE MYEMPLOYEE ME
  SET ME.SALARY = (
    SELECT CASE WHEN ME.SALARY > E.SALARY THEN ME.SALARY ELSE E.SALARY END 
    FROM EMPLOYEE E 
    WHERE ME.EMPNO = E.EMPNO
 )
 WHERE EXISTS(
   SELECT 1 
   FROM EMPLOYEE E 
   WHERE ME.EMPNO = E.EMPNO
 );

Then do an insert where the employee don't exist in the master table.

INSERT INTO MYEMPLOYEE ME
  SELECT * 
  FROM EMPLOYEE E 
    LEFT OUTER JOIN MYEMPLOEE ME ON E.EMPNO=ME.EMPNO
  WHERE ME.EMPNO IS NULL;

If you need to do in one full sweep you can use the IMPORT command. But then you are dealing with files. You would need to export the EMPLOYEE table (probably with salary already formatted) and then import using the INSERT_REPLACE ability.

Upvotes: 1

Related Questions