Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
        . . .;
share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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)
        . . .;
share|improve this answer
    
Yours is the correct syntax I was not paying attention. Yours is a +1 !!! –  RolandoMySQLDBA Apr 16 '12 at 3:46
    
It's definitely no less verbose. However, it benefits from being much more maintainable since the literals only have to be specified once each. +1 and accepted. –  Dennis Williamson Apr 18 '12 at 16:09
add comment

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

share|improve this answer
    
Option 1 had no effect. Option 2 (my original version) works, but is unpleasantly verbose. Thanks for your assistance. –  Dennis Williamson Jan 11 '12 at 16:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.