Hello!

there is a table for 150K rows with warehouse nomenclature

table view:

Activity | Warehouse | Cell-Off | Cell-Do | Capacity | Owner | Region | Timestamp

once a month there is an update of the table and it is necessary to synchronize them

1) deactivate not found lines

2) check if the owner has not changed on the matching lines in Warehouse | Cell-Off | Cell-Up

3) add new rows with data that has not yet been

I was looking for ready-made algorithms, but I didn’t find it, tell me how to do it correctly?

Thank you in advance!

  • Pour data into another temporary table with the same structure. insert into duplicate key update into the main table from a temporary one. Execute delete from основная_таблица where not exists(select из временной) table delete from основная_таблица where not exists(select из временной) or clean the table altogether and replace it with a new version. - Mike
  • Thanks for the answer! But INSERT ... ON DUPLICATE KEY UPDATE works the same for PRIMARY KEY, and the columns Warehouse | Cell-Off | Cell-Till PRIMARY KEY and Unique - berap
  • But if they are not unique, how are you going to look for the “very” record to make sure it is there or not. If there is some logic to search for a single record, then a unique index can be built using these fields - Mike
  • By the way, you can without a temporary table. add the field "version" can be a number, maybe a date. When insert / update set today's date or a new number. After that, all records of the old version are to be deleted - Mike
  • Uniqueness is defined by a combination of values, i.e. by combination Warehouse | Cell-Off | Cell-Do nothing useful in searching for "INSERT ... ON DUPLICATE UPDATE multi KEY" - berap

0