0

I have an Insert Statement like:

f"INSERT INTO `system_measurements`(`Global_irradiance_tilted_in_Wh_per_m2`, `a_id`, `subDate`) VALUES ('{temp}', '{temp_id}', '{i.date()}')"

And want it to ignore existing entries without checking the date everytime. So i thouhgt I could use

ON DUPLICATE KEY UPDATE a_id=a_id

But it still adds all values to the table.

why me
  • 301
  • 1
  • 2
  • 15

2 Answers2

1

I interpret your question as saying that a new row is inserted despite the on duplicate key.

In order for on duplicate key to work, you need a unique constraint or index. The update takes place when the query violates the unique constraint.

I am guessing that you want this on a_id, so be use you have something like:

alter table system_measurements add constraint unq_ system_measurements_a_id
    unique (a_id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

INSERT IGNORE will do nothing other than discovering that it is a duplicate. "Duplicate" is checked via the PRIMARY KEY and any UNIQUE keys.

Simply stick IGNORE after INSERT in the SQL you have.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I read about INSERT IGNORE that it's not recommended: https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – why me Feb 02 '21 at 12:43
  • But do any of the caveats in that other Q&A apply here? – Rick James Feb 02 '21 at 17:55