MySQL merge data on duplicate key

Question

I'm adopting dashboard now and I created two tables for selecting from frontend;

DATA_SELECTED_HISTORY
DATA_SELECTED_NOW

My frontend page get data from DATA_SELECTED_NOW and my backend algorithm put new data to this database.

I want to put my new data to DATA_SELECTED_NOW,
and the former data to be pushed to DATA_SELECTED_HISTORY when being faced with duplicate key.

I think I could use a swap table solution or insert(select subquery) + insert on duplicate key solution, but I don't get an idea anymore.

How can I use this solution in SQL?

Answer 1

you can use trigger in this case, to check duplication before insert to DATA_SELECTED_NOW and insert in DATA_SELECTED_HISTORY if it duplicates, check the below code

CREATE TRIGGER TRIGGER_Name
BEFORE INSERT ON DATA_SELECTED_NOW
FOR EACH ROW
BEGIN
  IF (EXISTS(SELECT 1 FROM User WHERE key = NEW.Key)) THEN
  --  you can replace "key = NEW.Key " with your logic to check
   -- inset into DATA_SELECTED_HISTORY  
  END IF;
END$$