MYSQL: Insert record if it is already exists update record

Question

I have a requirement to update a record if it exists else insert it. I already tried previous related answers but those are not sufficient

I have a table name with data_meta

meta_id   |    token   |   meta_key   |   meta_value
1                 1     terms_conditions    terms conditions content
2                 1     is_config_enable       1
3                 2     terms_conditions    terms conditions content

I have to update based on token & meta_key.. For same token meta_key doesn't repeat..

My Insert Query

"INSERT INTO data_meta (token, meta_key, meta_value)
                        VALUES (" . $token . ", '" . $key . "', '" . $value . "')";

My Update Query

 "UPDATE data_meta  SET  meta_value = '" . $value . "' WHERE  meta_key = '" . $key . "' AND token=" . $token ;

Why were the other answers not sufficient? - Why can't you just do a simple check with `SELECT` ? and then insert / update based on the result of that select? — Epodax, Aug 25 '16 at 07:30
**WARNING**: This has some severe [SQL injection bugs](http://bobby-tables.com/) because `$_GET` data is used inside the query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. — tadman, Aug 25 '16 at 07:53
Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — ToVine, Aug 25 '16 at 08:26

score 0 · Answer 1 · answered Aug 25 '16 at 07:40

MySQL provides the REPLACE INTO statement just for this.

REPLACE INTO data_meta (token, meta_key, meta_value) VALUES ( x, y, z );

For this to work , the combination of (token, meta_key) should be either primary key, or you need to create a unique index for the combination of (token, meta_key)

score 0 · Answer 2 · answered Aug 25 '16 at 07:40

Clearly your meta_id is your auto-increase index.

You forgot to use it in your update query. That query won't work without it, the update needs a WHERE condition.

Once you grasped this, the solution is obvious. So how does it work?

If you don't know the meta_id you insert, if you do know it you can update. It's as simple as that.

Mike · Answer 3 · 2016-08-25T10:54:12.803

0

create uniqe index on (token, meta_key) If not exists:

create unique index data_meta_tm on data_meta(token, meta_key);

Query:

"INSERT INTO data_meta (token, meta_key, meta_value)
                        VALUES (" . $token . ", '" . $key . "', '" . $value . "')
     ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)";

edited Aug 25 '16 at 10:54

answered Aug 25 '16 at 08:52

Mike

1,985
1
8
14

This does not do any updating, that is 'change something', since the `meta_value` is duplicated. – KIKO Software Aug 25 '16 at 10:42
@KIKOSoftware Why ? function VALUES in MySQL ON UPDATE clause get value of specified column from insert values parts. – Mike Aug 25 '16 at 10:46
You are right. At: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html it says that it only checks whether the values in a UNIQUE index or PRIMARY KEY would cause a duplicate. I assumed it would check all columns. You cannot just read this SQL, you have to know the table structure. – KIKO Software Aug 25 '16 at 10:49
PS: `DUPLACATE` should be: `DUPLICATE`. It's a good solution, I think. If I had read it properly I should have seen that it says: `ON DUPLICATE **KEY**`. – KIKO Software Aug 25 '16 at 10:53

MYSQL: Insert record if it is already exists update record

3 Answers3

Linked