CODEWITHSUNDEEP
mysql
Titan
Titan

Reputation: 6040

Increment field value on REPLACE

I have done this successfully with an UPDATE statement before but not a REPLACE.

I am saving favourite items in a mysql table when a user has checked out.

Table Favs is:

USER (int)
ITEM (int)
COUNT (int default 0)

The SQL I am trying is :

REPLACE INTO favs (user,item,count) VALUES ('1','3', count + 1)

although it does not throw any errors it does not seem to increment the value either.

Is this possible? Thank you.

Upvotes: 12

Views: 8339

Answers (1)

Thilo
Thilo

Reputation: 17735

Looks like it doesn't work like this on replace. From the manual:

You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.

Edit:

However, INSERT ... ON DUPLICATE UPDATE might do what you're trying to accomplish:

INSERT INTO favs (user, item) VALUES (2, 3) 
  ON DUPLICATE KEY UPDATE count = count + 1;

Upvotes: 28

Related Questions