Adding a value to an existing SQL value in a column using C++

Question

How would I make this code snippet add 10 to the value of "goals"?

if (buffer.empty())
    break;

buffer.erase(buffer.size() - 1);
async_query("insert into " + db_name("files") + " ("
                           + db_name("player") + ", "
                           + db_name("goals") + ", "
                           + db_name("completed") + ", "
                           + db_name("fid") + ") values " + buffer
                           + " on duplicate key update" + "  "
                           + db_name("player") + " = values("
                           + db_name("player") + ")," + "  "
                           + db_name("goals") + " = values("
                           + db_name("goals") + ")," + "  "
                           + db_name("completed") + " = values("
                           + db_name("completed") + "),"
                           + "  mtime = unix_timestamp()");
buffer.clear();

Answer 1

First of all, this is pure MySQL. It won't work on other SQL engines.

Besides, our question is pretty ambiguous:

Do you want the new value of goals to be incremented by 10 if the insert hits an existing fid, or retain the old value and increment it by 10?
Or do you want to put the new value of goal + 10 in both cases?

A bit of cleanup first

Once the C++ obfuscation removed, your query boils down to :

insert into $db_name (player, goals, completed, fid) values $buffer 
on duplicate key update
    player    = values(player), 
    goals     = values(goals), 
    completed = values(completed),
    mtime     = unix_timestamp()

(variables have been represented by $xxx)

I assume fid is the unique index or primary key that will trigger the on duplicate key.

I hope you don't have multiple unique indexes, or else the request will possibly modify only the first record that matches the entered values.

Assuming fid is the key, what your request do is erase the player, goals and completed fields (and update a timestamp) if a duplicate fid is passed.

This seems an ungodly thing to do to me, but surely you have your reasons.

I also wonder why you don't update the timestamp if a new record is created, but that's not the object of your question...

So basically you have 3 possibilities here:

1) set goals to new goal + 10 in any case

This would require to tweak the contents of your mysterious buffer, to add 10 to the new goal value.

Your code would then work without other modifications.

2) set goals to a new value for a new record, and to the same new value + 10 for an old record

insert into $db_name (player, goals, completed, fid) values $buffer 
on duplicate key update
    player    = values(player), 
    goals     = values(goals) + 10, 
    completed = values(completed),
    mtime     = unix_timestamp()

3) set goals to a new value for a new record, and increment the old goals value by 10 for an old record

insert into $db_name (player, goals, completed, fid) values $buffer 
on duplicate key update
    player    = values(player), 
    goals     = goals + 10, 
    completed = values(completed),
    mtime     = unix_timestamp()

Since I fail to understand what purpose is served by this query, I leave you the choice of the solution.