DuckDB更新JSON导入表时出现Binder Error：引用列未找到

场景重现 #

JSON数据源 #

[{"account": "abcde", "data": [{"name": "hey", "amount":1, "flow":"INFLOW"}, {"name": "hello", "amount":-2, "flow": null}]}, {"account": "hijkl", "data": [{"name": "bonjour", "amount":1, "flow":"INFLOW"}, {"name": "hallo", "amount":-3, "flow":"OUTFLOW"}]}]

建表代码 #

import duckdb

duckdb.sql("""
CREATE OR REPLACE TABLE mytable AS SELECT * FROM "example2.json"
""")

错误的UPDATE语句 #

duckdb.sql("""
UPDATE mytable SET data = NULL WHERE account = "abcde"
""")

报错信息 #

---------------------------------------------------------------------------
BinderException                           Traceback (most recent call last)
Cell In[109], line 1
----> 1 duckdb.sql("""
      2 UPDATE mytable SET data = NULL WHERE account = "abcde"
      3 """)
      6 # duckdb.sql("""
      7 # DELETE FROM mytable WHERE account = "abcde"
      8 # """)
     10 duckdb.sql("""
     11 SELECT * FROM mytable
     12 """)

BinderException: Binder Error: Referenced column "abcde" not found in FROM clause!
Candidate bindings: "mytable.data"
LINE 2: ...mytable SET data = NULL WHERE account = "abcde"
                                                  ^

解决方案 #

将UPDATE语句中字符串常量的双引号替换为单引号：

duckdb.sql("""
UPDATE mytable SET data = NULL WHERE account = 'abcde'
""")

原因解释 #

DuckDB遵循SQL标准语法规则：

• 双引号用于引用标识符（比如表名、列名、别名等）
• 单引号用于定义字符串常量

原语句中用双引号包裹abcde，DuckDB会将其识别为列名而非字符串值，因此抛出“找不到列abcde”的绑定错误。

内容的提问来源于stack exchange，提问作者charelf