SQLite Forum

content of field must not be identical to another column name of same table for UPDATE SET to work
Login

content of field must not be identical to another column name of same table for UPDATE SET to work

(1) By anonymous on 2020-09-09 11:07:55 [source]

UPDATE images SET w=700, h=498 WHERE dir="0203" and file="h";
UPDATE images SET w=397, h=600 WHERE dir="0241" and file="w";
UPDATE images SET w=400, h=600 WHERE dir="0326" and file="h";
UPDATE images SET w=1024, h=683 WHERE dir="0342" and file="h";

do not work: w, h remain NULL

renaming the columns works:

UPDATE images SET width=700, height=498 WHERE dir="0203" and file="h";
UPDATE images SET width=397, height=600 WHERE dir="0241" and file="w";
UPDATE images SET width=400, height=600 WHERE dir="0326" and file="h";
UPDATE images SET width=1024, height=683 WHERE dir="0342" and file="h";

CREATE TABLE images (
	dir CHAR[10] NOT NULL,
	file CHAR[50] NOT NULL,
	width INTEGER,
	height INTEGER,
	models TEXT,
	themes TEXT
);

(2.1) By Dan Kennedy (dan) on 2020-09-09 11:28:27 edited from 2.0 in reply to 1 [link] [source]

Unfortunately, this is the way it is. Use single-quotes for string literals.

https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted

(3) By Warren Young (wyoung) on 2020-09-09 11:28:41 in reply to 1 [link] [source]

Double quotes are for keywords, not identifiers. You either want single quotes here to indicate strings or backticks to quote column names.

You can recompile SQLite to force it to accept your syntax, but it's nonstandard SQL. Any SQL DB that accepts it has to do nonstandard magic to do the right thing in such cases, which can result in tears when its guess doesn't match your assumption of correct behavior.

It's far better to just use standard SQL, with its strictly defined behaviors.

(4) By Richard Damon (RichardDamon) on 2020-09-09 12:03:05 in reply to 3 [link] [source]

Double quotes are for keywords, not identifiers

You are misunderstanding the comment there (or I will give you that it is a bit awkwardly written).

Things inside single quotes are string constants by the SQL Standard. SQLite will treat it like this that is valid, but in some cases where it can't be a string constant, it will be tried as a column name

Things inside double quotes are identifiers (table or column names) by the SQL Standard. SQLite will treat it like this if it is valid, but if not, but a string literal is, then it will be treated as a string literal.

SQLite also allows for identifiers to be quoted as [id] pr id which isn't defined as such by the SQL standard by makes it more compatible with some other common engines.

It is best to always use single quotes for strings and double quotes for identifiers, that method follows the standard.