{question}
When I use ON DUPLICATE KEY UPDATE, I get the following error message:
ERROR 1706 (HY000): Feature 'UPDATE value depends on tables other than that being updated.' is not supported by MemSQL.
How should I use ON DUPLICATE KEY UPDATE?
{question}
{answer}
SingleStore supports the ON DUPLICATE KEY UPDATE command, but you need to use the VALUES() clause with it.
Here is a step-by-step example with and without the VALUES() clause:
create table tab1(name VARCHAR(32), id INT(11) PRIMARY KEY, orders INT(11));
create table tab2(name VARCHAR(32), id INT(11) PRIMARY KEY, orders INT(11));
insert into tab1 values ('Cindy', 7214, 2), ('Tom', 8301, 4), ('Adam', 3412, 5);
insert into tab2 values ('test1', 7214, 10);
memsql> select * from tab1;
+-------+------+--------+
| name | id | orders |
+-------+------+--------+
| Cindy | 7214 | 2 |
| Tom | 8301 | 4 |
| Adam | 3412 | 5 |
+-------+------+--------+
3 rows in set (0.03 sec)
memsql> select * from tab2;
+-------+------+--------+
| name | id | orders |
+-------+------+--------+
| test1 | 7214 | 10 |
+-------+------+--------+
1 row in set (0.02 sec)
Now when you try ON DUPLICATE KEY UPDATE without the VALUES() clause the following syntax you will get the error message:
memsql> insert into tab1 (name, id, orders)
-> select t2.name, t2.id, t2.orders
-> from tab2 t2
-> where t2.id=7214
-> on duplicate key update
-> orders = t2.orders;
ERROR 1706 (HY000): Feature 'UPDATE value depends on tables other than that being updated.' is not supported by MemSQL.
For it to work properly, you should use the VALUE() clause:
memsql> insert into tab1 (name, id, orders)
-> select t2.name, t2.id, t2.orders
-> from tab2 t2
-> where t2.id=7214
-> on duplicate key update
-> orders = values(orders);
Query OK, 2 rows affected (0.10 sec)
Records: 1 Duplicates: 1 Warnings: 0
memsql> select * from tab1;
+-------+------+--------+
| name | id | orders |
+-------+------+--------+
| Cindy | 7214 | 10 |
| Tom | 8301 | 4 |
| Adam | 3412 | 5 |
+-------+------+--------+
3 rows in set (0.03 sec)
{answer}