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.
-- 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,),
Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
In the case of a DML operation (such as MERGE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 18.104.22.168.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Not everyone who wants to help you knows anything about MySql or Python. It doesn't hurt to show how you're using those things, but try to phrase your question so that people can re-create and solve the problem entirely in Oracle.
In Oracle, naming columns exactly the same as functions makes things unnecessarily complicated. AVG, COUNT, MIN and MAX are all built-in functions, so it's best not to use those names for your columns. Use column names that have no special meaning to Oracle, such as CNT, MY_AVG or MIN_ID.
Well, there are at least a couple of things wrong with the sql part of what you posted
Firstly I don't think your USING query could possibly be that: if you are selecting from the datapoint table, how could you find things that are not in the datapoint table?
Secondly, your update should be:
when matched then update set .... You don't need/want datapoint in there again.
Thirdly, the VALUES() keyword does not exist in Oracle. You would do something like:
d.min = case when e.min < d.min then e.min else d.min end