Update record if "title" already exists in table when inserting

Question

I have tag table in my MySql db like this.

Id    Title    RepeatCount
--------------------------
 1     Tag1        1
 2     Tag2        5
 3     Tag3        8

I want to insert record into table in PDO, If record with same Title not exist (Title is primary key) and if exist, Increase record RepeatCount.

Like this example:

prepare(
    "IF (:Title IN (SELECT Title FROM tag)) 
     THEN INSERT INTO tag (Title) VALUES (:Title)
     ELSE UPDATE tag SET RepeatCount = RepeatCount + 1"
);
execute(array(
    ":Title" => "MyTag"
));

Why not use `INSERT ON DUPLICATE KEY UPDATE` check http://stackoverflow.com/questions/11390093/how-can-i-use-on-duplicate-key-update-in-pdo-with-mysql — Saty, Dec 05 '15 at 11:47
just reorder to : 1. `update .. where title = ..` 2. `insert into .. where not in ..` — amdixon, Dec 05 '15 at 11:48
That is what do you search for: http://stackoverflow.com/questions/14797510/fastest-way-to-update-a-mysql-table-if-row-exists-else-insert-more-than-2-non-u — Gouda Elalfy, Dec 05 '15 at 11:56

score 1 · Accepted Answer · answered Dec 05 '15 at 12:38

In MySQL SQL, control flow statements such as if are only valid in stored blocks, such as stored procedures, functions, and triggers. A better way to do this process is to use on duplicate key update:

insert into tag(Title)
    values (:Title)
    on duplciate key update RepeatCount = RepeatCount + 1;

This is better because the database handles race conditions, so you don't have to worry about values being overwritten. Note: this assumes that RepeatCount is initialized to a number and not to NULL.

Update record if "title" already exists in table when inserting

1 Answers1