DuckDB Binder Error: Referenced column not found in FROM clause

Question

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.

Answer 1

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

Answer 2

When dealing with varchar in DuckDB, use single quotes '' instead of double quotes "".