Mysql - On Duplicate Key Insert A Different Value

Question

I have a table called history that tracks changes made to another table. The basic idea is to be able to track who last updated a specific entry.

When I insert the first time it will create an entry that shows the action field as 'UPLOAD'. The next time that same key is entered I need it to create a new history where the action is 'UPDATE', that way I can see when it was first made and who updated that key after that.

Unfortunately ON DUPLICATE KEY INSERT... is not a mysql construct. What do I do?

So far I have:

INSERT INTO history (key,userID,action,note,date)
VALUES (?,?,?,?,?)

*Where the value of action is always 'UPLOAD', even if it's an update ** The question marks are from the prepared statement bind

If the key exists in history I need the action to change to 'UPDATE' and create a new entry - it should never change entries that already exist.

Please elaborate on what you want to happen when a duplicate is inserted. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 8, 2016 at 3:23
You need set your key column is Primary Key and Auto Increament (as 1, 2, 4....) — GiapLee
– GiapLee, Commented Jan 8, 2016 at 3:23
@TimBiegeleisen When duplicate key is found create a new history entry where the action is 'UPDATE' instead of 'UPLOAD' — Kellen Stuart
– Kellen Stuart, Commented Jan 8, 2016 at 3:29

Alex · Accepted Answer · 2016-01-08 03:33:33Z

4

You can use triggers

CREATE TRIGGER catchHim BEFORE INSERT ON myTable
FOR EACH ROW
  BEGIN
    IF EXISTS(SELECT * FROM myTable WHERE userID=NEW.userID) THEN
        INSERT INTO history (key,userID,action,note,date) VALUES (NEW.myColumn1, NEW.myColumnForUserId, .....);
    END IF;
  END;

But they are hard to keep track of, especially that nobody uses them on MySQL.

Actually verify if that thing exists, in PHP, before making the update, then deciding whether to insert history or not.

edited Jan 8, 2016 at 3:33

answered Jan 8, 2016 at 3:31

Alex

14.7k5 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kellen Stuart Over a year ago

I was thinking about verifying if it exists prior to inserting, but I feel it will take too long querying sql from php, (these values coming in are from a huge Excel Spreadsheet).

Alex Over a year ago

That's unavoidable. You either do it explicitly, or the DB engine does it before sending you off with an error. But it's fast, since it's probably a primary key, therefore a clustered index. So don't worry.

Kellen Stuart Over a year ago

Cool! I'll give it a try. Thanks for your help!

Collectives™ on Stack Overflow

Mysql - On Duplicate Key Insert A Different Value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related