I am trying to write a query in sqlite3 that will search my database table for a match, then create a column according to if it finds a match or not.
This is what my code looks like:
import sqlite3
conn = sqlitet3.connect('mydatabase.db')
cur = conn.cursor()
query = '''
CREATE TABLE test AS SELECT
identifier,
MAX(CASE WHEN identifier == "ABC123" THEN 1 ELSE 0 AS id_flag)
'''
cur.execute(query)
print(cur.fetchone())
The MAX statement is meant to collapse duplicate values. For instance, if there are two rows with the identifier of ABC123, then the query would search for the max of the id_flags and output just one row with that identifier.
Thus, my expected output (and the output when I write it in regular SQL) is along the lines of this:
Identifier id_flag
XYZ234 0
ABC123 1
QRS789 0
This gives a syntax error "near "AS"".
If I remove the AS portion (and the id_flag afterwards) and run the code, then the syntax error moves to "near ")"".
If I remove the parentheses, then the syntax error moves to "near "CASE"".
I've looked over the sqlite3 syntax flowchart but I can't figure out how to structure this query and not get the syntax error.
Thanks for your help!
Your query is missing FROM clause, and a GROUP BY clause. Presumably you have a source table (say src), and you want one row per distinct identifier, along with a column that indicates whether duplicate exists.
That would be:
create table test as
select identifier, count(*) > 1 as id_flag
from src
group by identifier