Robin Beismann Robin Beismann - 4 years ago 226
SQL Question

SQL Update, if not exist then insert

Table Layout:

CREATE TABLE gmod_playerdata (
dataset_id int(255) NULL AUTO_INCREMENT,
data_id int(255) NULL,
option_name varchar(100) NULL,
option_value varchar(100) NULL,
PRIMARY KEY (dataset_id)
)


I can't know whether the row already exists or not.
data_id
will always be filled, but can/will appear multiple times.

I only need to update option_name and option_value, while I don't know if a option_value and option_name already exist when I run the query.

I've read much about
ON DUPLICATE KEY UPDATE
however I kinda don't get how it is meant to be used in my scenario.

Answer Source

Assuming combinations of option_value and option_name are unique...

For on duplicate key update to work, there must be a unique index (or a primary key), so:

create unique index myidx on gmod_playerdata(option_value, option_name);

Once the index is created, this query will work:

insert into gmod_playerdata values (...)
on duplicate key update data_id = values(data_id)

I have assumed since data_id is the only other real column, that you want to update that instead of inserting a row. If that isn't what you want, adjust the query following the syntax.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download