CODEWITHSUNDEEP
phpmysql
Kellen Stuart
Kellen Stuart

Reputation: 8893

Mysql - On Duplicate Key Insert A Different Value

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.

Upvotes: 0

Views: 304

Answers (1)

Alex
Alex

Reputation: 14618

  1. 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.

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

Upvotes: 4

Related Questions