[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

[Chris Prakoso]

Hi all,

I just joined the Mailing List yesterday, so apologise for any mistake
I am doing.

I'm a .NET (C#) Developer, and at the moment I'm coding a small app
with SQLite as the backend database.

My question is whether anybody had successfully implemented multiple
rows Insert/Update.

I've tried to use INSERT OR REPLACE and passing multiple VALUES, but
it didn't seem to work.

Any pointers would be appreciated.


Thanks very much,
Chris

[Clemens Ladisch]

Post by Chris Prakoso
My question is whether anybody had successfully implemented multiple
rows Insert/Update.

This is possible in SQL:

INSERT INTO MyTable(ID, Value)
VALUES (1, 'hello'),
(2, 'world');

UPDATE MyTable
SET Value = 'the same value'
WHERE ID IN (1, 2);

-- rather verbose; better use two simple UPDATEs:
UPDATE MyTable
SET Value = CASE ID
WHEN 1 THEN 'new A'
WHEN 2 THEN 'new B'
END
WHERE ID IN (1, 2);


Regards,
Clemens

[Chris Prakoso]

Hi Clemens,

Thanks for your reply. I've tried to use raw SQL but it didn't work
either. Do you have any SQLite front-end that you use?


Regards,
Chris

Post by Clemens Ladisch

Post by Chris Prakoso
My question is whether anybody had successfully implemented multiple
rows Insert/Update.

INSERT INTO MyTable(ID, Value)
VALUES (1, 'hello'),
(2, 'world');
UPDATE MyTable
SET Value = 'the same value'
WHERE ID IN (1, 2);
UPDATE MyTable
SET Value = CASE ID
WHEN 1 THEN 'new A'
WHEN 2 THEN 'new B'
END
WHERE ID IN (1, 2);
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Chris Prakoso
Hi Clemens,
Thanks for your reply. I've tried to use raw SQL but it didn't work
either.

Please show us the SQL that you did you.

Post by Chris Prakoso
Do you have any SQLite front-end that you use?

The only supported "front-end" (if I correctly understand your
meaning) is the command-line tool "sqlite3.exe" available from the
https://www.sqlite.org/download.html page. There are many other
third-party tools, most of which are GUI-based. You can use them if
you want, and they usually work quite well, but occasionally have
bugs.

[Chris Prakoso]

I did a test using simple table, and entering the sql directly using SQLite
Administrator:

*insert into test (field1,field2) values (1,"two"),(2,"three")*


The error I got from the SQLite Administrator is:

*2/9/2016 11:29:40 AM: SQL Error: near ",": syntax error <insert into test
(field1,field2) values (1,"two"),(2,"three")>*

Thanks,
Chris

Post by Richard Hipp

Post by Chris Prakoso
Hi Clemens,
Thanks for your reply. I've tried to use raw SQL but it didn't work
either.

Please show us the SQL that you did you.

Post by Chris Prakoso
Do you have any SQLite front-end that you use?

The only supported "front-end" (if I correctly understand your
meaning) is the command-line tool "sqlite3.exe" available from the
https://www.sqlite.org/download.html page. There are many other
third-party tools, most of which are GUI-based. You can use them if
you want, and they usually work quite well, but occasionally have
bugs.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Clemens Ladisch]

Post by Chris Prakoso
insert into test (field1,field2) values (1,"two"),(2,"three")
SQL Error: near ",": syntax error

You might want to update to a tool that is not years out of date.


Regards,
Clemens

[Richard Hipp]

Post by Clemens Ladisch

Post by Chris Prakoso
insert into test (field1,field2) values (1,"two"),(2,"three")
SQL Error: near ",": syntax error

You might want to update to a tool that is not years out of date.

What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20)
the INSERT statement would only take a single row in the VALUES
clause. He is suggesting that you are using a version of SQLite that
is 4 year old or older and therefore lacks this feature.

[Chris Prakoso]

Yes thank you. My SQLite is the latest, it's the front-end that is
outdated, which I have just swiftly corrected.

Regards,
Chris

Post by Richard Hipp

Post by Clemens Ladisch

Post by Chris Prakoso
insert into test (field1,field2) values (1,"two"),(2,"three")
SQL Error: near ",": syntax error

You might want to update to a tool that is not years out of date.

What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20)
the INSERT statement would only take a single row in the VALUES
clause. He is suggesting that you are using a version of SQLite that
is 4 year old or older and therefore lacks this feature.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Salih Yücel]

Hi,
I have windowns phone project but one problem . Sqli te select result data type text column turkish characters encoding problem
But Android operation system no problem turkish characters


Salih Yücel
Mobil Grup Lideri /Mobile Group Leader

Univera Bilgisayar Sistemleri San. ve Tic. A.Ş.

________________________________________
Bu elektronik posta ve onunla iletilen bütün dosyalar sadece göndericisi tarafından alması amaçlanan yetkili gerçek ya da tüzel kişinin kullanımı içindir.Eğer söz konusu yetkili alıcı değilseniz bu elektronik postanın içeriğini açıklamanız, kopyalamanız, yönlendirmeniz ve kullanmanız kesinlikle yasaktır ve bu elektronik postayı derhal silmeniz gerekmektedir. UNİVERA A.Ş. bu mesajın içerdiği bilgilerin doğruluğu veya eksiksiz olduğu konusunda herhangi bir garanti vermemektedir. Bu nedenle bu bilgilerin ne şekilde olursa olsun içeriğinden, iletilmesinden, alınmasndan ve saklanmasından sorumlu değildir. Bu mesajdaki görüşler yalnzca gönderen kişiye aittir ve UNİVERA A.Ş.'nin görüşlerini yansıtmayabilir. Bu e-posta bilinen bütün bilgisayar virüslerine karşı taranmıştır.
________________________________________
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any dissemination, forwarding, copying or use of any of the information is strictly prohibited, and the e-mail should immediately be deleted. UNIVERA A.S. makes no warranty as to the accuracy or completeness of any information contained in this message and hereby excludes any liability of any kind for the information contained therein or for the information transmission, reception, storage or use of such in any way whatsoever. The opinions expressed in this message belong to sender alone and may not necessarily reflect the opinions of UNIVERA A.S. This e-mail has been scanned for all known computer viruses.
________________________________________


-----Original Message-----
From: sqlite-users-***@mailinglists.sqlite.org [mailto:sqlite-users-***@mailinglists.sqlite.org] On Behalf Of Chris Prakoso
Sent: Tuesday, February 9, 2016 5:09 PM
To: SQLite mailing list <sqlite-***@mailinglists.sqlite.org>
Subject: Re: [sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

Yes thank you. My SQLite is the latest, it's the front-end that is outdated, which I have just swiftly corrected.

Regards,
Chris

Post by Richard Hipp

Post by Clemens Ladisch

Post by Chris Prakoso
insert into test (field1,field2) values (1,"two"),(2,"three")
SQL Error: near ",": syntax error

You might want to update to a tool that is not years out of date.

What Clemens means by this is that prior to SQLite 3.7.11 (2012-03-20)
the INSERT statement would only take a single row in the VALUES
clause. He is suggesting that you are using a version of SQLite that
is 4 year old or older and therefore lacks this feature.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

Post by Salih Yücel
Hi,
I have windowns phone project but one problem . Sqli te select result data type text column turkish characters encoding problem
But Android operation system no problem turkish characters
Salih Yücel
Mobil Grup Lideri /Mobile Group Leader
Univera Bilgisayar Sistemleri San. ve Tic. A.Ş.

(I'm just re-posting this under a new heading to not confuse with the
other thread.)

[Simon Slavin]

Post by Chris Prakoso
*insert into test (field1,field2) values (1,"two"),(2,"three")*

As well as the comments about your software being out of date, you need to know that the text delimiter in SQLite is the non-directional single quote character normally seen as an apostrophe. You cannot successfully use the double quote character or any directional quotes.

Should be

insert into test (field1,field2) values (1,'two'),(2,'three')

Simon.

[Chris Prakoso]

Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
double-quote as delimiter successfully.

Regards,
Chris

Post by Simon Slavin

Post by Chris Prakoso
*insert into test (field1,field2) values (1,"two"),(2,"three")*

As well as the comments about your software being out of date, you need to
know that the text delimiter in SQLite is the non-directional single quote
character normally seen as an apostrophe. You cannot successfully use the
double quote character or any directional quotes.
Should be
insert into test (field1,field2) values (1,'two'),(2,'three')
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Chris Prakoso
Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
double-quote as delimiter successfully.

That is supported for backwards compatibility. I originally put in
support for double-quoted string literals to be compatible with MySQL
3.5. I have long since regretted that decision. You are encouraged
to use single-quotes for string literals, as double-quotes have a very
different meaning that can lead to subtle bugs.

[Chris Prakoso]

Ok. Thanks for the reminder.

Regards,
Chris

Post by Richard Hipp

Post by Chris Prakoso
Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
double-quote as delimiter successfully.

That is supported for backwards compatibility. I originally put in
support for double-quoted string literals to be compatible with MySQL
3.5. I have long since regretted that decision. You are encouraged
to use single-quotes for string literals, as double-quotes have a very
different meaning that can lead to subtle bugs.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

That's not an SQLitespeed feature but indeed a backwards-compatible
SQLite feature. (I had this wrong too at some point)

You probably already know, but to be clear: In SQL standard,
double-quotes indicate identifiers and single quotes indicate string values.
While the single quotes are used more or less universally, identifiers
often get quoted differently.
In Postgres and SQLite the standard is adhered to with Double quotes.
MySQL uses a back-tick like this ` (which, by the way, will also work in
SQLite due the same backward compatibility)
MSSQL likes the square brackets [ and ] around identifiers (which,
again, also works in SQLite for the same reason)

Further to this, in earlier MySQL and in SQLite you may also use double
quotes to denote strings, and it will regard a double-quoted value to be
a string if A - it isn't an identifier, or B - used in a place where you
can't use an identifier. (You can just imagine the bugs in your SQL that
can arise from this!)

While all of the above works, you are strongly encouraged to simply do
it correctly and use double-quotes for identifiers and single quotes for
strings.

We have been lobbying for a strict-mode in SQLite where none of these
shenanigans are allowed - but that is far easier said than done.

Post by Chris Prakoso
Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
double-quote as delimiter successfully.
Regards,
Chris

Post by Simon Slavin

Post by Chris Prakoso
*insert into test (field1,field2) values (1,"two"),(2,"three")*

As well as the comments about your software being out of date, you need to
know that the text delimiter in SQLite is the non-directional single quote
character normally seen as an apostrophe. You cannot successfully use the
double quote character or any directional quotes.
Should be
insert into test (field1,field2) values (1,'two'),(2,'three')
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Chris Prakoso]

Thanks for the detailed explanation.

Regards,
Chris

That's not an SQLitespeed feature but indeed a backwards-compatible SQLite
feature. (I had this wrong too at some point)
You probably already know, but to be clear: In SQL standard, double-quotes
indicate identifiers and single quotes indicate string values.
While the single quotes are used more or less universally, identifiers
often get quoted differently.
In Postgres and SQLite the standard is adhered to with Double quotes.
MySQL uses a back-tick like this ` (which, by the way, will also work in
SQLite due the same backward compatibility)
MSSQL likes the square brackets [ and ] around identifiers (which, again,
also works in SQLite for the same reason)
Further to this, in earlier MySQL and in SQLite you may also use double
quotes to denote strings, and it will regard a double-quoted value to be a
string if A - it isn't an identifier, or B - used in a place where you
can't use an identifier. (You can just imagine the bugs in your SQL that
can arise from this!)
While all of the above works, you are strongly encouraged to simply do it
correctly and use double-quotes for identifiers and single quotes for
strings.
We have been lobbying for a strict-mode in SQLite where none of these
shenanigans are allowed - but that is far easier said than done.

Post by Chris Prakoso
Actually I've just done it now, in SQLiteSpeed, and it allowed me to use
double-quote as delimiter successfully.
Regards,
Chris

Post by Chris Prakoso
*insert into test (field1,field2) values (1,"two"),(2,"three")*
As well as the comments about your software being out of date, you need to
know that the text delimiter in SQLite is the non-directional single quote
character normally seen as an apostrophe. You cannot successfully use the
double quote character or any directional quotes.
Should be
insert into test (field1,field2) values (1,'two'),(2,'three')
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________

sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

Post by Chris Prakoso
Hi Clemens,
Thanks for your reply. I've tried to use raw SQL but it didn't work
either. Do you have any SQLite front-end that you use?

If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add
your DB file, open it and then use the buttons on the far right (such as
"Insert", "Update", "Delete", etc.) to produce the SQL that will insert
to or update the selected table. You can then see the correct syntax and
expand on it to add your own values etc.

You could also right-click on the results list and select from the menu
"Row Actions"-->"Show UPDATE SQL for changing this row" or "Show INSERT
SQL for copying this row" etc.
There are a myriad more auto-SQL things in there, perhaps a good option
if you are learning SQL still.

You could also right-click in a query and then select "Copy as
Code"-->"Java"/"C#"/ whatever you like, or add your own codify settings
for your preferred language to take the pain out of adapting queries to
code.

The INSERT and UPDATE functions you described should really work in SQL,
so perhaps it's a small syntax error or such preventing success.


(PS: This list is not a support group for SQLitespeed or any other gui,
you may use its own bug/query reporter from the menu to get help there)

Cheers,
Ryan

[Chris Prakoso]

Thanks Ryan,

I will definitely try it. I'm ok with raw SQL, just not familiar with the
odd ones like this multiple rows update.

Thanks a lot,
Chris

Post by Chris Prakoso
Hi Clemens,
Thanks for your reply. I've tried to use raw SQL but it didn't work
either. Do you have any SQLite front-end that you use?

If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add your
DB file, open it and then use the buttons on the far right (such as
"Insert", "Update", "Delete", etc.) to produce the SQL that will insert to
or update the selected table. You can then see the correct syntax and
expand on it to add your own values etc.
You could also right-click on the results list and select from the menu
"Row Actions"-->"Show UPDATE SQL for changing this row" or "Show INSERT SQL
for copying this row" etc.
There are a myriad more auto-SQL things in there, perhaps a good option if
you are learning SQL still.
You could also right-click in a query and then select "Copy as
Code"-->"Java"/"C#"/ whatever you like, or add your own codify settings for
your preferred language to take the pain out of adapting queries to code.
The INSERT and UPDATE functions you described should really work in SQL,
so perhaps it's a small syntax error or such preventing success.
(PS: This list is not a support group for SQLitespeed or any other gui,
you may use its own bug/query reporter from the menu to get help there)
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users