Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Equivalent of MySQL Update ON DUPLICATE Query

jstem1177Jan 20 2014 — edited Jan 21 2014

Hello All,

I'm trying to get build up some equivalence update queries from MySQL to Oracle.

Unfortunetely, I must also tackle this in python. I'm just curious if somebody might have an idea, mainly on the MERGE USING.

Any assitance would help. Woudl have posted this on MySQL forums, but it looks like its being spammed lately.

Jan S.

-- MYSQL

conn.execute("""INSERT INTO datapoint VALUES %s

                ON DUPLICATE KEY UPDATE      

                avg = (count * avg + VALUES(avg)) / (count + 1),

                count = count + 1,

                min = IF(min < VALUES(min), min, VALUES(min)),

                max = IF(max > VALUES(max), max, VALUES(max))""" % (values,),

--ORACLE

conn.execute("""MERGE INTO datapoint d

    USING ( select %s FROM datapoint) e ON (d.datapoint_id = e.datapoint_id)

        WHEN MATCHED THEN

          UPDATE datapoint SET     

                        avg = (count * avg + VALUES(avg)) / (count + 1),       

            count = count + 1,

            min = CASE WHEN min < VALUES(min) THEN min ELSE VALUES(min) END;,

            max = CASE WHEN max > VALUES(max) THEN max ELSE VALUES(max) END;

        WHEN NOT MATCHED THEN

          INSERT INTO datapoint VALUES %s""" % (values,),

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 18 2014
Added on Jan 20 2014
3 comments
499 views