1

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?

Community
  • 1
  • 1
Konservin
  • 909
  • 1
  • 9
  • 19

1 Answers1

2

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);
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • If I try to add unique index to user_ID, phpMyAdmin gives an error "#1062 - Duplicate entry '36' for key 'user_ID'". As for storing comma-delimeted list: it's for storing DataTable preferences after logout, which are mostly arrays. – Konservin Jan 28 '17 at 15:10
  • @Konservin . . . The unique index has two keys, not one. Look at the example in the answer. – Gordon Linoff Jan 28 '17 at 15:15
  • My bad. Still: insert into test(user_ID, name, val) values (36, 'colvis', '3') on duplicate key update set val = concat(val, ',', values(val)); gives error "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set val = concat(val, ',', values(val))' at line 3" INSERT INTO AK_user_vars (user_ID, name, value) VALUES(36, 'colvis', '3,5,6,7,9') ON DUPLICATE KEY UPDATE user_ID=36, name='colvis', value='3,5,6,7,10'; doesn't give error but no update either. – Konservin Jan 28 '17 at 15:35
  • @Konservin . . . My bad. There is no `set` in the statement. I just think there *should* be. – Gordon Linoff Jan 28 '17 at 15:40
  • Perfect, thank You: insert into test(user_ID, name, value) values (36, 'colvis', '3,5,6,7,9') on duplicate key update value = '3,5,6,7,13'; worked as a charm (row updated), and insert into test(user_ID, name, value) values (39, 'colvis', '3,5,6,7,9') on duplicate key update value = '3,5,6,7,9'; worked too (row inserted). Problem solved. Just one small question: for some reason, when a row was updated, phpMySql said "2 rows inserted" not "1 row updated". Why is that? – Konservin Jan 28 '17 at 15:49
  • @Konservin . . . Perhaps you have a trigger on the table. – Gordon Linoff Jan 28 '17 at 15:50
  • 'There are no triggers to display' says phpMyAdmin. It's really not relevant at all to the question, I'm just curious. – Konservin Jan 28 '17 at 16:15