Simultaneous INSERT and UPDATE MySQL Query

Asked Jan 14 '14 at 01:43

Active Jan 14 '14 at 01:48

Viewed 203 times

What I'd like to say is something like:

Update a count by 1 if an id exists add it if not.

I have tried the following:

INSERT INTO links (id, count) VALUES (:id, 1)
       ON DUPLICATE KEY UPDATE count = count + 1;

which is some PHP code, I can add the ids, but I can't increment the count. Any ideas please?

edited Jan 14 '14 at 01:48

Niels Keurentjes

asked Jan 14 '14 at 01:43

user1408643

2

Is `id` defined to be a primary key or unique? – Gordon Linoff Jan 14 '14 at 01:45
1

Your code should work fine if `id` is properly assigned as primary key. – Niels Keurentjes Jan 14 '14 at 01:48
I do not `mysql` has this functionality in oracle `merge into`, although mysql has `replace` syntax, it is not as good as `merge` – Rugal Jan 14 '14 at 01:49
@Rugal `replace` performs a delete-then-reinsert, in this case the content needs to be updated so `insert ... on duplicate key update ...` is the correct functionality to use. – Niels Keurentjes Jan 14 '14 at 01:50
@NielsKeurentjes cool! thanks, I need this syntax! – Rugal Jan 14 '14 at 01:51
@Rugal It works thanks! – user1408643 Jan 14 '14 at 01:56
Possible duplicate of http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql?rq=1 – Jan 14 '14 at 02:26

0 Answers0