mysqlon-duplicate-key

MySQL merge data on duplicate key


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?


Solution

  • 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$$