On 14-4-2019 09:36, Shawn Wagner wrote:
Discovered this tonight answering a question on stack overflow:
sqlite> create table foo(a, b);
sqlite> insert into foo(a,b,a,b) values(1,2,3,4);
sqlite> select * from foo;
a b
---------- ----------
1 2
Inserting a column multiple times only uses the first corresponding value.
I don't see this documented anywhere.
By contract, a single UPDATE of the same column multiple times uses the
last one and ignores the rest:
sqlite> update foo set a=3, a=4;
sqlite> select * from foo;
a b
---------- ----------
4 2
And that is documented.
The inconsistency is annoying, but changing how either one works will
doubtless break somebody's code. Maybe clarify INSERT's behavior in its
documentation? Logging a warning in the case of a column being used
multiple times might be nice too.
An error like this one should do:
sqlite> insert into foo values(1,2,3,4);
Error: table foo has 2 columns but 4 values were supplied
Because, i do think, that it would never be possible to specify more
than the number of columns in an insert statement?
MS-SQL has this:
create table foo(a int, b int);
insert into foo(a,b,a,b) values(1,2,3,4);
Msg 264, Level 16, State 1, Line 3
The column name 'a' is specified more than once in the SET clause or
column list of an INSERT. A column cannot be assigned more than one
value in the same clause. Modify the clause to make sure that a column
is updated only once. If this statement updates or inserts columns into
a view, column aliasing can conceal the duplication in your code.
Msg 264, Level 16, State 1, Line 3
The column name 'b' is specified more than once in the SET clause or
column list of an INSERT. A column cannot be assigned more than one
value in the same clause. Modify the clause to make sure that a column
is updated only once. If this statement updates or inserts columns into
a view, column aliasing can conceal the duplication in your code.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
