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