How do you make the last insert in this fiddle increment beyond 2?
mysql mysql-5.5 add tag
Howard Brown 
I have a question about this fiddle:

<>https://dbfiddle.uk?rdbms=mysql_5.5&fiddle=522f4e13a6cbf42b7d7f35446e5a4c3e

I'm creating a table similar to the one in this fiddle that tracks the number of times that an image is added to it.  The first insert should set the test column to 1, and subsequent inserts are converted to updates that increment test by 1 each time to show that the inserted image is being used multiple times.  Later, not shown in the fiddle, when an image is deleted, the test value should be decremented by 1 and not actually deleted unless test is 0 after the decrement.

However, it looks like inserting the same image more than once doesn't actually increment as I describe, above.  Instead test only increments to 2 when the row is updated.

Is there a way to make the ON DUPLICATE KEY UPDATE phrase work the way I want?

Thank you
Top Answer
Jack Douglas 
From [the docs](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html):

> In assignment value expressions in the `ON DUPLICATE KEY UPDATE` clause, you can use the `VALUES(col_name)` function to refer to column values from the `INSERT` portion of the `INSERT ... ON DUPLICATE KEY UPDATE` statement. In other words, `VALUES(col_name)` in the `ON DUPLICATE KEY UPDATE` clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts…

It looks like you just want to increment the existing value in the table like this:

<>https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=bd56543ac4f6a82c9c2f27fb9d66fa60
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.