0

I want to INSERT INTO MySQL or UPDATE MySQL if exist without having to run 2 different queries.

I have checked Create if an entry if it doesn't exist, otherwise update?

INSERT INTO table (a) VALUES (0)  ON DUPLICATE KEY UPDATE

but it only works if you check doubles on the key. The issue is I want to check on another field that is not the key, but yet has so stay unique. I want to ADD or UPDATE on checking the 'symbol' in my dictionary:

The data I get is (example)

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

The INSERT INTO I want to adapt is:

mycursor.executemany(f"INSERT INTO `param_forex` (Ticker,Price) 
                    VALUES ( %(symbol)s, %(price)s)", forex)
mydb.commit()

I do NOT want to use the REPLACE because of the auto increment field (REPLACE deletes and inserts) The column I am checking for duplicates is Ticker in the table and symbol in the dictionary.

I am stuck. Is there a solution?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Mat
  • 65
  • 6
  • 2
    Why isn't there a unique index on this column if it has to stay unique? – Barmar May 22 '23 at 16:40
  • If you can't use `ON DUPLICATE KEY` then you have to use multiple queries. – Barmar May 22 '23 at 16:42
  • Its always best to at least give us a bit of a clue by showing us at least an example of the query. It stops us having to guess from a poorly described question what you actually mean. – RiggsFolly May 22 '23 at 16:46
  • Seems like a redesign of the database is what is really required here – RiggsFolly May 22 '23 at 16:51
  • Searching past answers, I found this same question has been asked many times over many years. Example from 2010: https://stackoverflow.com/questions/3180913/mysql-update-on-duplicate-key-without-a-unique-column Most of the answers are: _"add a unique constraint on the column you want to be unique, then use `INSERT ... ON DUPLICATE KEY UPDATE`."_ – Bill Karwin May 22 '23 at 17:05
  • The alternative is to run two queries, one to check if the row exists, then conditionally execute INSERT or UPDATE. Example from 2015: https://stackoverflow.com/questions/33495194/mysql-insert-on-duplicate-update-for-non-primary-key – Bill Karwin May 22 '23 at 17:07
  • *The issue is I want to check on another field that is not the key, but yet has so stay unique.* If the column must be unique then you must create according unique index. This is the only way to check the data consistency. *I do NOT want to use the REPLACE because of the auto increment field (REPLACE deletes and inserts)* REPLACE needs in unique constraint too. – Akina May 22 '23 at 18:10
  • @RiggsFolly, I thought my question was pretty explanatory, and the accompagning query as well. I am only new so lacking some subtilities maybe. I don't think it deserved a -1 though... – Mat May 22 '23 at 19:28
  • I added a index on Ticke ans it does work. Thanks all – Mat May 22 '23 at 19:28

1 Answers1

1

You can use the INSERT ... ON DUPLICATE KEY UPDATE statement, but as mentioned in the comments, you need to have a unique index on the 'Ticker' column. For example:

forex = [{'symbol': 'EURUSD', 'price': '1.06763000'}, {'symbol': 'GBPEUR', 'price': '0.90339600'}, {'symbol': 'EURJPYC', 'price': '0.000011200'}]

query = "INSERT INTO `param_forex` (Ticker, Price) VALUES (%(symbol)s, %(price)s) ON DUPLICATE KEY UPDATE Price = VALUES(Price)"

mycursor.executemany(query, forex)
mydb.commit()

To make this work, just alter the table to include a unique key:

ALTER TABLE `param_forex` ADD UNIQUE INDEX `idx_Ticker` (`Ticker`);
James
  • 834
  • 7
  • 27