# 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](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/performing-upserts.md) with a reference table that has a primary key. For example, `upsert_1` is a reference table with a primary key.
  ```sql
  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
  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;
  ```

> **⚠️ 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. 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.

***

Modified at: April 30, 2024

Source: [/db/v9.1/reference/troubleshooting-reference/query-errors/error-1706-hy-000-feature-multi-table-update-delete-with-a-reference-table-as-target-table-is-not-supported-by-memsql/](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/query-errors/error-1706-hy-000-feature-multi-table-update-delete-with-a-reference-table-as-target-table-is-not-supported-by-memsql/)

(An index of the documentation is available at /llms.txt)