Search code examples
mysqlsqlinsertsql-deleteexists

INSERT new row or DELETE old row IF it already exists

I need to optimize/implement a MYSQL query wich do the following:

I need to store if an user marked an item as seen, and create a SEEN/UNSEEN button, that delete the correspondent row on the database if already exists (is marked as seen) OR insert a new row if it not (is not marked).

It means, I need a mysql query that do that:

    DELETE FROM table WHERE userid = ? AND itemid = ?

or that

    INSERT INTO table (userid, itemid) VALUES (?,?)

depending on if the row already exists or not.

I now it's very easy doing it with php and checking the number of rows of a SELECT before INSERT or DELETE but I would like to doing that all the optimized I can.

Solution

  • You can use stored procedure to SELECT and DELETE if exists or INSERT. But much easier would just be left the data there and update seen/unseen with ON DUPLICATE KEY UPDATE.