Converting an Oracle merge query into mysql mySQL query

Question

    MERGE INTO PAGEEDITCONTROL A
      USING (SELECT
              '1585' AS PAGEID
       ,'admin' AS EDITUSER
       ,sysdate AS EDITDATE
          FROM DUAL) B
      ON (A.PAGEID = B.PAGEID)
      WHEN MATCHED THEN
      UPDATE SET
      A.EDITUSER = B.EDITUSER
     ,A.EDITDATE = B.EDITDATE
      WHEN NOT MATCHED THEN
   INSERT  (
      A.PAGEID
     ,A.EDITUSER
     ,A.EDITDATE
      )VALUES(
      B.PAGEID
     ,B.EDITUSER
     ,B.EDITDATE
      )

score 2 · Answer 1 · answered Apr 08 '13 at 09:12

2

INSERT INTO  
pageeditcontrol (pageid, edituser, editdate) 
VALUES  ('1585', 'admin', sysdate()) 
ON DUPLICATE KEY UPDATE 
edituser = values(edituser), 
editdate = values(editdate);

answered Apr 08 '13 at 09:12

laksys

3,228
4
27
38

According to http://stackoverflow.com/a/548570/1563700 this does a delete and then an insert. The delete may be cascaded to foreign keys and delete triggers may be executed. This could modify your database in a way you are not expecting it – Nov 11 '13 at 15:10
1

@mklemenz The delete and insert talked about in the answer pertains to the REPLACE statement not the INSERT INTO ON DUPLICATE KEY UPDATE – knutesten Dec 19 '13 at 13:27

score 1 · Answer 2 · answered Dec 27 '08 at 13:21

1

MySQL has a similar construct which looks less like an upsert, but more like an insate: INSERT ... ON DUPLICATE KEY UPDATE (see the MySQL reference manual).

answered Dec 27 '08 at 13:21

Henning

16,063
3
51
65

Converting an Oracle merge query into mysql mySQL query

2 Answers2