Update the column in case of duplicate in mysql-CodePudding

So I am storing some config values in my table which looks like

name   value 
secret  1223
url     "url"

Now I have a situation where I want to update the secret keys value column in case if the user re inserts it ,or allow any other config values to be inserted properly

like say INSERT INTO table (name,value) Values('secret',"new secret"); so the table should look like

name   value 
secret  1223
url     "new secret"

But if I do something like

INSERT INTO table (name,value) Values('new attribute',"value");

  name   value 
    secret  1223
    url     "new secret"
    new attribute  "value"

A new row gets added

I tried this

  INSERT INTO table (name,value) Values('secret',"new secret"); ON DUPLICATE KEY UPDATE value=VALUES(value)

But thats not working

CodePudding user response：

I am sure it didn't work because of syntax errors.

This should be working fine

INSERT INTO table (name,value) Values('secret','new secret') ON DUPLICATE KEY UPDATE value=VALUES('new secret')

db_fiddle

CodePudding user response：

ON DUPLICATE KEY UPDATE might be worked on unique or primary key constraints on a table.

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.

if we insert a new row which create unique or primary key constraints it might be worked

BTW I think the script might be like this, the semicolon need to put at last

INSERT INTO table (name,value) Values('secret',"new secret") ON DUPLICATE KEY UPDATE value=VALUES(value);

sqlfiddle