CODEWITHSUNDEEP
phpmysql
Konservin
Konservin

Reputation: 1017

MySql Table Insert if not exist otherwise update on non-unique columns

In a table like this:

ID | user_ID | name     | value
1  | 36      | order    | {1, 'asc'}
2  | 36      | colvis   | 0,1,2,4,7
3  | 36      | colorder | 0,1,2,4,3,5,6,7
4  | 37      | colvis   | 0,1,2,4,5,7

where only ID is unique and AUTO_INCREMENT, I need to INSERT new row for user_ID=36 and colvis='something', if row 'colvis' for that user doesn't exist already, i.e do something like 'INSERT IF NOT EXIST user_ID=x AND name=y ELSE UPDATE' query.

For example, if I have user_ID=36 and name='colorder', it should update row 3, but with user_ID=37 and name='colorder' it should insert new row. Same with user_ID=36 and name='filter', it should insert a new row.

It can be done with

$exist = $sql->Query("SELECT * FROM test WHERE user_ID = 36 AND name='colvis'");
if ($exist) {
  //UPDATE
} else {
  // INSERT
}

But isn't there a one-liner?

I looked at Insert into a MySQL table or update if exists and MySQL procedure to create or update a row in a table and MySql Insert if not exist two column pair otherwise update, but those are not applicable unless I have 'user_ID' and 'name' columns unique, are they?

Upvotes: 1

Views: 1394

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are looking for insert . . . on duplicate key update. But first, you need a unique index:

create unique index unq_test_userID_name on test(user_ID, name)

Then you can do an insert:

insert into test(user_ID, name, value)
    values (36, 'colvis', '3')
    on duplicate key update value values(val);

A big caveat here: Storing multiple values in a comma-delimited list is almost never the right thing to do. There are rare exceptions to this rule, which is why I've answered the question.

The right solution is probably a junction table, TestValues, with one row per "test" and one row per "value". In that case, what you want to do is easy:

insert into TestValues(test_id, value)
    values (test_id, val);

Upvotes: 2

Related Questions