SQLite syntax error that I can't figure out - SOLVED
-
I'm pulling my hair with this one: (There's a unique index on the [DOMAIN] field.
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE DNSLOG
SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'The error is clearly in the ON CONFLICT clause because when I run only the INSERT clause, it does what it's supposed to do. And the UPDATE statement works when I run it by itself. The actual error message is "SQL Logic error near "DNSLOG" syntax error". Any idea what the syntax error could be? ****SOLUTION**** It's not supposed to have the table name in the "DO UPDATE" statement. IOW:
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'The difficult we do right away... ...the impossible takes slightly longer.
-
I'm pulling my hair with this one: (There's a unique index on the [DOMAIN] field.
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE DNSLOG
SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'The error is clearly in the ON CONFLICT clause because when I run only the INSERT clause, it does what it's supposed to do. And the UPDATE statement works when I run it by itself. The actual error message is "SQL Logic error near "DNSLOG" syntax error". Any idea what the syntax error could be? ****SOLUTION**** It's not supposed to have the table name in the "DO UPDATE" statement. IOW:
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'The difficult we do right away... ...the impossible takes slightly longer.
-
There is no
DOoption for ON CONFLICT: [The ON CONFLICT Clause](https://www.sqlite.org/lang\_conflict.html)Keep Calm and Carry On
Thanks for your response. What about this page? This says that there is: SQLite Syntax: upsert-clause[^] This is the INSERT statement, it clearly shows the upsert clause: SQLite Syntax: insert-stmt[^]
The difficult we do right away... ...the impossible takes slightly longer.
