ERROR 1706 (HY000): Feature Multi-table UPDATE/DELETE with a reference table as target table is not supported by MemSQL


Updating a reference table based on a join with a sharded table returns an error. This is because reference tables are only updatable on the master aggregator while joins against sharded tables must run on the leaves, so the query cannot run like normal updates.


  • Use the same table types that use both the target as well as joined table as sharded tables or as reference tables.

  • Use an upsert with a reference table that has a primary key. For example, upsert_1 is a reference table with a primary key.

    CREATE TABLE upsert_2(a INT, b INT);

    The following upsert from upsert_1 into upsert_2 runs successfully:

    INSERT INTO upsert_1
    SELECT upsert_1.* FROM upsert_1
    JOIN upsert_2 ON upsert_1.b = upsert_2.b
    ON DUPLICATE KEY UPDATE upsert_1.b = VALUES(b)+1;


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. This removes the common row lock deadlock (transaction A waiting for transaction B on partition 1, but B waiting for A on partition 2) at the cost of potentially letting failed queries commit partial results.

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: April 30, 2024

Was this article helpful?