# Failed to update from joined table

**URL:** <https://community.cratedb.com/t/failed-to-update-from-joined-table/1845>\
**Category:** CrateDB\
**Tags:** sql\
**Created:** [September 11, 2024, 8:23am UTC](https://community.cratedb.com/t/failed-to-update-from-joined-table/1845 "2024-09-11T08:23:12Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jun\_Zhou](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jun_zhou/32/1079_2.png) [@Jun\_Zhou](https://community.cratedb.com/u/Jun_Zhou)\
**Post date:** [September 11, 2024, 8:23am UTC](https://community.cratedb.com/t/failed-to-update-from-joined-table/1845/1 "2024-09-11T08:23:12Z")

</div>

Hi Team, I’m using CrateDB 5.5.2 and tried to update a field in a table with values obtained from joining another table. However, I encountered the following error, but I don’t know the exact cause.

```auto
UPDATE Ebs.Rwmv_Xxmtl_Transactions
   SET Posted_By =
       (SELECT Ebs.Fnd_User.Description
          FROM Ebs.Mtl_Material_Transactions
          JOIN Ebs.Fnd_User
            ON Ebs.Fnd_User.User_Id = Ebs.Mtl_Material_Transactions.Created_By
         WHERE Ebs.Mtl_Material_Transactions.Transaction_Id = Ebs.Rwmv_Xxmtl_Transactions.Transaction_Id)
 WHERE Ebs.Rwmv_Xxmtl_Transactions.Posted_By IS NULL;

```

error message

```auto
ERROR: Relation 'ebs.rwmv_xxmtl_transactions' unknown
 at position ：io.crate.analyze.relations.FullQualifiedNameFieldProvider.resolveField(FullQualifiedNameFieldProvider.java:165)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitQualifiedNameReference(ExpressionAnalyzer.java:944)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitQualifiedNameReference(ExpressionAnalyzer.java:444)
io.crate.sql.tree.QualifiedNameReference.accept(QualifiedNameReference.java:38)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitComparisonExpression(ExpressionAnalyzer.java:777)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitComparisonExpression(ExpressionAnalyzer.java:444)
io.crate.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:80)
io.crate.analyze.expressions.ExpressionAnalyzer.convert(ExpressionAnalyzer.java:245)
io.crate.analyze.expressions.ExpressionAnalyzer.lambda$generateQuerySymbol$1(ExpressionAnalyzer.java:254)
java.base/java.util.Optional.map(Optional.java:260)
io.crate.analyze.expressions.ExpressionAnalyzer.generateQuerySymbol(ExpressionAnalyzer.java:254)
io.crate.analyze.relations.RelationAnalyzer.visitQuerySpecification(RelationAnalyzer.java:423)
io.crate.analyze.relations.RelationAnalyzer.visitQuerySpecification(RelationAnalyzer.java:115)
io.crate.sql.tree.QuerySpecification.accept(QuerySpecification.java:101)
io.crate.analyze.relations.RelationAnalyzer.visitQuery(RelationAnalyzer.java:150)
io.crate.analyze.relations.RelationAnalyzer.visitQuery(RelationAnalyzer.java:115)
io.crate.sql.tree.Query.accept(Query.java:72)
io.crate.analyze.relations.RelationAnalyzer.analyze(RelationAnalyzer.java:133)
io.crate.analyze.expressions.SubqueryAnalyzer.analyze(SubqueryAnalyzer.java:41)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitSubqueryExpression(ExpressionAnalyzer.java:1120)

```

table definition

```auto
+--------------------------------------------------------------+
| SHOW CREATE TABLE ebs.rwmv_xxmtl_transactions |
+--------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "ebs"."rwmv_xxmtl_transactions" ( |
| "id" VARCHAR(48) NOT NULL, |
| "transaction_id" VARCHAR(480), |
| "transaction_date" VARCHAR(480), |
| "transaction_type_name" VARCHAR(480), |
| "subinventory_code" VARCHAR(480), |
| "transfer_subinventory" VARCHAR(480), |
| "creation_date" VARCHAR(480), |
| "rcv_transaction_id" VARCHAR(480), |
| "organization_id" VARCHAR(480), |
| "project_id" VARCHAR(480), |
| "transfer_project_id" VARCHAR(480), |
| "organization_name" VARCHAR(480), |
| "item_code" VARCHAR(480), |
| "lot_number" VARCHAR(480), |
| "subinventory_name" VARCHAR(480), |
| "transfer_subinventory_name" VARCHAR(480), |
| "project_number" VARCHAR(480), |
| "transaction_quantity" INTEGER, |
| "transaction_reference" VARCHAR(480), |
| "transaction_source_name" VARCHAR(480), |
| "posted_by" VARCHAR(480), |
| "item_desc" VARCHAR(480), |
| "product_model" VARCHAR(480), |
| "pur_cate" VARCHAR(480), |
| "uom" VARCHAR(480), |
| "source_name" VARCHAR(480), |
| "reference_number" VARCHAR(480), |
| "reference_type" VARCHAR(480), |
| "reference_crt_name" VARCHAR(480), |
| "notice_header_remarks" VARCHAR(4000), |
| "dlvy_header_remarks" VARCHAR(4000), |
| PRIMARY KEY ("id") |
| ) |
| CLUSTERED BY ("id") INTO 4 SHARDS |
| WITH ( |
| "allocation.max_retries" = 5, |
| "blocks.metadata" = false, |
| "blocks.read" = false, |
| "blocks.read_only" = false, |
| "blocks.read_only_allow_delete" = false, |
| "blocks.write" = false, |
| codec = 'default', |
| column_policy = 'strict', |
| "mapping.total_fields.limit" = 1000, |
| max_ngram_diff = 1, |
| max_shingle_diff = 3, |
| number_of_replicas = '0-1', |
| refresh_interval = 1000, |
| "routing.allocation.enable" = 'all', |
| "routing.allocation.total_shards_per_node" = -1, |
| "store.type" = 'fs', |
| "translog.durability" = 'REQUEST', |
| "translog.flush_threshold_size" = 536870912, |
| "translog.sync_interval" = 5000, |
| "unassigned.node_left.delayed_timeout" = 60000, |
| "write.wait_for_active_shards" = '1' |
| ) |
+--------------------------------------------------------------+

cr> show create table Ebs.Fnd_User;
+-----------------------------------------------------+
| SHOW CREATE TABLE ebs.fnd_user |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "ebs"."fnd_user" ( |
| "user_id" BIGINT, |
| "user_name" TEXT, |
| "description" TEXT |
| ) |
| CLUSTERED INTO 4 SHARDS |
| WITH ( |
| "allocation.max_retries" = 5, |
| "blocks.metadata" = false, |
| "blocks.read" = false, |
| "blocks.read_only" = false, |
| "blocks.read_only_allow_delete" = false, |
| "blocks.write" = false, |
| codec = 'default', |
| column_policy = 'strict', |
| "mapping.total_fields.limit" = 1000, |
| max_ngram_diff = 1, |
| max_shingle_diff = 3, |
| number_of_replicas = '0-1', |
| refresh_interval = 1000, |
| "routing.allocation.enable" = 'all', |
| "routing.allocation.total_shards_per_node" = -1, |
| "store.type" = 'fs', |
| "translog.durability" = 'REQUEST', |
| "translog.flush_threshold_size" = 536870912, |
| "translog.sync_interval" = 5000, |
| "unassigned.node_left.delayed_timeout" = 60000, |
| "write.wait_for_active_shards" = '1' |
| ) |
+-----------------------------------------------------+
SHOW 1 row in set (0.002 sec)

cr> show create table Ebs.Mtl_Material_Transactions;
+----------------------------------------------------------------+
| SHOW CREATE TABLE ebs.mtl_material_transactions |
+----------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "ebs"."mtl_material_transactions" ( |
| "transaction_id" TEXT, |
| "created_by" TEXT |
| ) |
| CLUSTERED INTO 4 SHARDS |
| WITH ( |
| "allocation.max_retries" = 5, |
| "blocks.metadata" = false, |
| "blocks.read" = false, |
| "blocks.read_only" = false, |
| "blocks.read_only_allow_delete" = false, |
| "blocks.write" = false, |
| codec = 'default', |
| column_policy = 'strict', |
| "mapping.total_fields.limit" = 1000, |
| max_ngram_diff = 1, |
| max_shingle_diff = 3, |
| number_of_replicas = '0-1', |
| refresh_interval = 1000, |
| "routing.allocation.enable" = 'all', |
| "routing.allocation.total_shards_per_node" = -1, |
| "store.type" = 'fs', |
| "translog.durability" = 'REQUEST', |
| "translog.flush_threshold_size" = 536870912, |
| "translog.sync_interval" = 5000, |
| "unassigned.node_left.delayed_timeout" = 60000, |
| "write.wait_for_active_shards" = '1' |
| ) |
+----------------------------------------------------------------+
SHOW 1 row in set (0.002 sec)

```

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [September 11, 2024, 9:11am UTC](https://community.cratedb.com/t/failed-to-update-from-joined-table/1845/2 "2024-09-11T09:11:57Z")

</div>

If you run just the select

```sql
SELECT
  Ebs.Fnd_User.Description
FROM
  Ebs.Mtl_Material_Transactions
  JOIN Ebs.Fnd_User ON Ebs.Fnd_User.User_Id = Ebs.Mtl_Material_Transactions.Created_By
WHERE
  Ebs.Mtl_Material_Transactions.Transaction_Id = Ebs.Rwmv_Xxmtl_Transactions.Transaction_Id

```

You get the same error  
` [9/11/2024, 11:04:11 AM]: RelationUnknown[Relation 'ebs.rwmv_xxmtl_transactions' unknown]`

`ebs.fnd_user` is being joined with `mtl_material_transactions` but on the conditional `WHERE` you are also introducing `Rwmv_Xxmtl_Transactions` which is not known by cratedb at that point since it’s not used in the join.

To simplify:

```auto
SELECT
  *
FROM
  a
  JOIN b ON b.foo = a.foo
WHERE
  a.foo = c.foo

```

```auto
 [9/11/2024, 11:11:01 AM]: RelationUnknown[Relation 'doc.c' unknown]

```

Is not valid