ERROR 1706 (HY000): Feature Multi-table UPDATE/DELETE with a reference table as target table is not supported by MemSQL
Issue
Updating a reference table based on a join with a sharded table returns an error.
Solutions
-
Use the same table types that use both the target as well as joined table as sharded tables or as reference tables.
-
Use
UPSERT
with a reference table that has a primary key.For example, if t1
is a reference table with a primary key, then the following query runs successfully:INSERT INTO t1 SELECT t1.* FROM t1 JOIN t2 ON t1.b = t2.b ON DUPLICATE KEY UPDATE t1.b.For more details, refer to the UPDATE topic.
Warning
Turning off multi-statement transactions makes per-partition components of a distributed transaction commit independently without waiting for other partitions to finish and potentially trigger rollback.
If the values inserted are dependent on multiple statements executing concurrently, then inconsistent data may get inserted.
In case of failure, it is not possible to rollback the entire multi-upsert inside one transaction.
Last modified: July 5, 2021