“on duplicate key update” all the non-key fields mentioned in the insert

Question

How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion?

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = 'fieldvalue'
        . . .;

Answer 1

You can use the VALUES(column) function:

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = VALUES(fieldname)
        . . .;

Answer 2

You have one of two options:

OPTION # 1

You may want to experiment by adding an additional UNIQUE KEY

ALTER TABLE tablename ADD UNIQUE KEY (keyname,fieldname);

Then try running your same UPDATE without fieldname like this

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        . . .;

If that does not produce the desired behavior, then there is OPTION 2

OPTION # 2

In terms of SQL synntax, you simply have no other other option

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = 'fieldvalue'
        . . .;

What you stated in the question is really all you can do