I am working in DuckDB in a database that I read from json.
Here is the 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"
}
]
}
]
I am opening it in Python as follows:
import duckdb
duckdb.sql("""
CREATE OR REPLACE TABLE mytable AS SELECT * FROM "example2.json"
""")
This all works fine and I get a copy of my table, but then I try to update it:
duckdb.sql("""
UPDATE mytable SET data = NULL WHERE account = "abcde"
""")
which crashes with
---------------------------------------------------------------------------
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"
^
I have searched the documentation and the error but I just can't find what I am doing wrong here.
I actually solved the issue. I had to use single quotes '
instead of double quotes "
in the string comparison...
Solution
duckdb.sql("""
UPDATE mytable SET data = NULL WHERE account = 'abcde'
""")
correctly does
┌─────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐
│ account │ data │
│ varchar │ struct("name" varchar, amount bigint, flow varchar)[] │
├─────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤
│ hijkl │ [{'name': bonjour, 'amount': 1, 'flow': INFLOW}, {'name': hallo, 'amount': -3, 'flow': OUTFLOW}] │
│ abcde │ NULL │
└─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘
Interestingly, ChatGPT helped me spot this mistake. (There is a ban on posting AI answers, but it's OK if they are human-verified).