CODEWITHSUNDEEP
phpmysqlif-statementpdo
Mohammad
Mohammad

Reputation: 21489

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

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"
));

Upvotes: 1

Views: 178

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 1

Related Questions