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. 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.

Solutions

• 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.

  SQL

  Copy

  CREATE REFERENCE TABLE upsert_1(a INT PRIMARY KEY, b INT);
  CREATE TABLE upsert_2(a INT, b INT);

  The following upsert from upsert_1 into upsert_2 runs successfully:

  SQL

  Copy

  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;