SQL Update, if not exist then insert

Question

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 1

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.