Re: [sqlite] Is it a bug or "as designed"?

[Simon Slavin]

On 10 Mar 2018, at 7:15am, John Found  wrote:

> Simon Slavin  wrote:
> 
>> On 9 Mar 2018, at 7:49pm, John Found  wrote:
>> 
>>> In the current implementation "insert or replace" behave as the foreign 
>>> constraint is deferred.
>>> But according to documentation, all foreign constraints in SQLite are 
>>> immediate by default.
>> 
>>create table B (
>>aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
>>ulti_data)
> 
> No difference at all. Because "insert or replace" always works as if the 
> constraint is deferred. 
> "insert or replace" always succeed to delete rows that are referenced by B 
> and defers the constraint enforcement until
> the end of the internal transaction. But at the end, a new row with the same 
> ID is inserted, so there is no 
> constraint violation anymore. 

John, I apologise.  I missed a paragraph in the documentation:

"If the current statement is not inside an explicit transaction (a 
BEGIN/COMMIT/ROLLBACK block), then an implicit transaction is committed as soon 
as the statement has finished executing. In this case deferred constraints 
behave the same as immediate constraints."

So please keep the CREATE TABLE statement the same as quoted above, but change 
your data commands to

BEGIN;
   insert or replace into A values (?1, ?2);
   insert into B values (?1, ?2);
COMMIT;

or even

BEGIN;
   insert or replace into A values (?1, ?2);
COMMIT;
insert into B values (?1, ?2);

Do these make things work the way you expect ?

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

Re: [sqlite] Is it a bug or "as designed"?

[John Found]

On Sat, 10 Mar 2018 01:17:38 +
Simon Slavin  wrote:

> On 9 Mar 2018, at 7:49pm, John Found  wrote:
> 
> > In the current implementation "insert or replace" behave as the foreign 
> > constraint is deferred.
> > But according to documentation, all foreign constraints in SQLite are 
> > immediate by default.
> 
> John,
> 
> The documentation suggests that in SQLite foreign keys are not deferred by 
> default.  Section 4.2 of
> 
> 
> 
> talks about this and shows how to set up a foreign key to be deferred:
> 
> create table B (
> aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
> ulti_data)
> 

No difference at all. Because "insert or replace" always works as if the 
constraint is deferred. 
"insert or replace" always succeed to delete rows that are referenced by B and 
defers the constraint enforcement until
the end of the internal transaction. But at the end, a new row with the same ID 
is inserted, so there is no 
constraint violation anymore. 


> Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite 
> "inserts" them around that single statement) I /think/ this should allow your 
> code to work the way you intended.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it a bug or "as designed"?

[Simon Slavin]

On 9 Mar 2018, at 7:49pm, John Found  wrote:

> In the current implementation "insert or replace" behave as the foreign 
> constraint is deferred.
> But according to documentation, all foreign constraints in SQLite are 
> immediate by default.

John,

The documentation suggests that in SQLite foreign keys are not deferred by 
default.  Section 4.2 of



talks about this and shows how to set up a foreign key to be deferred:

create table B (
aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
ulti_data)

Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite 
"inserts" them around that single statement) I /think/ this should allow your 
code to work the way you intended.

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

[sqlite] Site error

[R Smith]

It seems to me this is a temporary thing, perhaps due to someone working 
on the site, but...


I've opened sqlite.org, clicked "Search", then entered "foreign key" as 
the search term and clicked Go.


It produced the error below.
I've re-tried several times, different searches, problem remained.
Example url: https://sqlite.org/search?s=d&q=table


 Wapp Application Error

attempt to write a readonly database
while executing
"db2 eval {
PRAGMA synchronous=OFF;
PRAGMA journal_mode=OFF;
BEGIN;
  CREATE TABLE IF NOT EXISTS log(
ip,  -- I..."
(procedure "search_add_log_entry" line 7)
invoked from within
"search_add_log_entry $nRes"
(procedure "searchresults" line 49)
invoked from within
"$cmd"
("uplevel" body line 1)
invoked from within
"uplevel {$cmd}"
invoked from within
"time [list uplevel $script]"
(procedure "ttime" line 2)
invoked from within
"ttime {$cmd}"
invoked from within
"db transaction {
set t [ttime {$cmd}]
  }"
(procedure "wapp-default" line 46)
invoked from within
"wapp-default"




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

Re: [sqlite] Is it a bug or "as designed"?

[R Smith]

On 2018/03/09 9:49 PM, John Found wrote:

On Fri, 9 Mar 2018 19:42:19 +
Simon Slavin  wrote:

You are right. And Jay Kreibich in his post above. But then the second 
solution from my post should be the correct behavior.

In the current implementation "insert or replace" behave as the foreign 
constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate 
by default.



If an FK is created as immediate, it will fail at the conclusion (read: 
END OF) the statement that hits the FK check. If it is declared as 
DEFERRED it will wait all the way until the end of the Transaction when 
you try to commit.


Either way, many people get confused with REPLACE because it feels like 
UPDATE, it is not, REPLACE means (as have been pointed out) "REMOVE 
constraint-violating rows, THEN, ADD the given new row" - and more 
importantly - all of this happens INSIDE one single statement so that 
any FK checks will happen at the end of said statement AFTER the new row 
is added back, and so no constraint violation exists come checking time.


Triggers are a whole different matter, they are like EVENT handlers, and 
have to fire by binding contract the very moment the triggerable offense 
happens, which in the case of REPLACE is right in the middle of the 
statement, if (and only if) there was one or more rows in there that 
needed ousting.


What you probably wanted is an "UPSERT", which in theory is more an 
UPDATE OR INSERT than a REPLACE, and will cause at best an UPDATE 
Trigger to fire and no deletions will happen.  SQLite doesn't have a 
command like that, but you can easily simulate it by just issuing two 
commands:
First do the UPDATE... WHERE Key = X - which, if the record doesn't 
exist yet, will fail quietly because of the WHERE clause,
then do the INSERT OR IGNORE(...) - which will again fail quietly if it 
did already exist,
both of which won't break a Transaction, mess with the wrong triggers or 
constraint checks, will be very fast (considering the PK is used and 
only one of the two statements gets to do any work in IO terms)... and 
will always work perfectly and reliably fire only ON INSERT, ON UPDATE 
and ON DELETE triggers when those really happen.


The obvious downside to it being you having to formulate both an UPDATE 
and an INSERT statement in your code, which can be irritating, but then, 
if it's the best tool for the job...



I hope this shed some light on how it works and why the triggers and FKs 
fire differently (by design) and how to get the results you want.


Cheers,
Ryan


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

Re: [sqlite] Is it a bug or "as designed"?

[Jay Kreibich]

> On Mar 9, 2018, at 1:42 PM, Simon Slavin  wrote:
> 

> "replace" means "delete the original row, then insert a new one”.

More properly, it means “delete any and all rows that might cause any conflict 
with inserting the new row.”  There really isn’t a concept of an “original” 
row, it just happens that the most common conflict is primary key.  It’s also 
true that inserting a single row with “insert or replace” can cause multiple 
rows to be deleted (if there are multiple constraints across multiple columns, 
for example).

 -j

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

Re: [sqlite] Is it a bug or "as designed"?

[John Found]

On Fri, 9 Mar 2018 19:42:19 +
Simon Slavin  wrote:

> On 9 Mar 2018, at 7:11pm, John Found  wrote:
> 
> > "insert or replace" succeed without deleting the old rows from B.
> 
> "replace" means "delete the original row, then insert a new one".
> 
> In your code, figure out whether you need INSERT or UPDATE, and do the 
> appropriate one.
> 

You are right. And Jay Kreibich in his post above. But then the second solution 
from my post should be the correct behavior. 

In the current implementation "insert or replace" behave as the foreign 
constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate 
by default.


> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it a bug or "as designed"?

[Simon Slavin]

On 9 Mar 2018, at 7:11pm, John Found  wrote:

> "insert or replace" succeed without deleting the old rows from B.

"replace" means "delete the original row, then insert a new one".

In your code, figure out whether you need INSERT or UPDATE, and do the 
appropriate one.

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

Re: [sqlite] Is it a bug or "as designed"?

[Jay Kreibich]

Foreign keys enforcement can get tricky depending on the enforcement policy, 
transactions, and a lot of things.  I don’t have enough experience to comment 
on that fully.


I will say this, however, because it is a common mistake with a lot of 
different aspects of database behavior:

“Insert or replace” is NOT “insert or update.”

Insert always inserts a new row.  The only question is if it deletes 
conflicting rows (yes, plural) first.



With that in mind, the FK concept gets a little fuzzy.  It is a new row that 
just happens to have the same ID as an old row.  Does that mean the FK in B 
should still reference it?  I’d say no, because it is a brand new row… a 
different entity. If you want B to keep referencing the existing row, then 
update the row that is already there, don’t delete it and insert a new row on 
top of it.  I’d look into deferrable constraints to see if the behavior you’re 
looking for is supported.  Hopefully others can comment more on that.

 -j





> On Mar 9, 2018, at 1:11 PM, John Found  wrote:
> 
> 
> I have two tables with foreign constraint:
> 
>create table A ( id primary key not null, single_data );
>create table B ( aid references A(id) on delete cascade, multi_data);
> 
> Now I am periodically inserting data in A and B with the following queries:
> 
>insert or replace into A values (?1, ?2);
>insert into B values (?1, ?2); 
> 
> Unfortunately, after replacing some row in A, all previously inserted rows in 
> B got deleted, even if the value of ID does not changes. Here SQLite works 
> exactly as it first deletes the conflicting row from A and then inserting new.
> 
> Now, if I define the table B without "on delete":
> 
>create table B ( aid references A(id), multi_data);
> 
> "insert or replace" succeed without deleting the old rows from B.
> 
> In my opinion this behaviour is not consistent. The consistent solutions IMHO 
> are two:
> 
> 1. "insert or replace" succeed both in the first and in the second case 
> without deleting rows from B,
> 
> 2. "insert or delete" succeed in the first case, deleting all constrained 
> rows from B and fails in the second case with "FOREIGN KEY constraint failed".
> 
> The first case IMHO is more intuitive and natural. At least this was my 
> expectation when writing the code.
> 
> 
> -- 
> http://fresh.flatassembler.net
> http://asm32.info
> John Found 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Is it a bug or "as designed"?

[John Found]

I have two tables with foreign constraint:

create table A ( id primary key not null, single_data );
create table B ( aid references A(id) on delete cascade, multi_data);

Now I am periodically inserting data in A and B with the following queries:

insert or replace into A values (?1, ?2);
insert into B values (?1, ?2); 

Unfortunately, after replacing some row in A, all previously inserted rows in B 
got deleted, even if the value of ID does not changes. Here SQLite works 
exactly as it first deletes the conflicting row from A and then inserting new.

Now, if I define the table B without "on delete":

create table B ( aid references A(id), multi_data);

"insert or replace" succeed without deleting the old rows from B.

In my opinion this behaviour is not consistent. The consistent solutions IMHO 
are two:

1. "insert or replace" succeed both in the first and in the second case without 
deleting rows from B,

2. "insert or delete" succeed in the first case, deleting all constrained rows 
from B and fails in the second case with "FOREIGN KEY constraint failed".

The first case IMHO is more intuitive and natural. At least this was my 
expectation when writing the code.


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help getting started

[Simon Slavin]

On 9 Mar 2018, at 4:42pm, Richard Hipp  wrote:

> On 3/9/18, Larry Mullings  wrote:
>> I have a SQLite Bible database.  It has
>> Bible verses with Strong's numbers and Hebrew.
> 
> Are you willing to share your database?  Can you send me a copy via
> private email attachment?

Anyone interested in this might also be interested in the ".sql" files listed 
here:



However, these are English and Greek, not Hebrew.

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

Re: [sqlite] Help getting started

[Richard Hipp]

On 3/9/18, Larry Mullings  wrote:
> I have a SQLite Bible database.  It has
> Bible verses with Strong's numbers and Hebrew.

Are you willing to share your database?  Can you send me a copy via
private email attachment?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help getting started

[Donald Griggs]

Hi Larry,

Since you mention sqlite3.exe, I assume you're on Windows.

Kudos for compiling your own exe, but if, in future, you find you don't
need special compile features, you can always download the current version
exe from http://sqlite.org/download.html  .

If I understand you correctly, you'll want to go to a Windows cmd prompt
screen,
then type
   sqlite3   YourDatabasename

This should open your database and give you a prompt for
sqlite sql commands and "dot" commands

https://sqlite.org/cli.html

There are also many windows GUI utilities (written by third-parties) which
you may find useful.

As to adding a column, the simplest method is to use the ALTER TABLE command
http://sqlite.org/lang_altertable.html
but you may also choose to copy the existing table into a newly-defined
table, then remove the old one and rename.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help getting started

[Simon Slavin]

On 9 Mar 2018, at 3:48pm, Larry Mullings  wrote:

> I'm in need of some serious help.  I'm a first timer to anything database.  I 
> have a SQLite Bible database.  It has
> Bible verses with Strong's numbers and Hebrew.  I'd like to add some fields 
> to the database.  I downloaded
> sqlite-amalgamation-322 and compiled it.  Now I have sqlite3.exe and .obj 
> files.  Now what do I need to do?

What operating system are you using ?  If its Windows or Macintosh, include the 
version number.

You probably already have a compiled version of the sqlite3 executable, but to 
tell you where to fine it we need to know your operating system.

Once you can actually run a copy of sqlite3.exe, see this page:



I advise you to experiment with a new blank file as the page shows, and learn 
how to do things first, before you start changing /a copy of/ your bible 
database.

If you have successfully got through sections 11 and 2 of this page, post again 
and someone will show you how to add more columns to an existing table.

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

[sqlite] Help getting started

[Larry Mullings]

I'm in need of some serious help.  I'm a first timer to anything database.  I 
have a SQLite Bible database.  It has
Bible verses with Strong's numbers and Hebrew.  I'd like to add some fields to 
the database.  I downloaded
sqlite-amalgamation-322 and compiled it.  Now I have sqlite3.exe and .obj 
files.  Now what do I need to do?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Richard Hipp]

On 3/9/18, Hegde, Deepakakumar (D.)  wrote:
>
> SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);
>

Here is a query that gives the rows in the order you desire:

  WITH a(x,y) AS (VALUES(3,1),(1,2),(2,3))
  SELECT newfolder.* FROM newfolder, a WHERE x=id ORDER BY y;

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Jim Callahan]

If the prefered ORDER BY clause is awkward;
How large is your table?
 and is it on a Solid State Disk (SSD) with low seek time?

If the table is small (less than 100,000 rows) and you are querying by an
indexed field (such as the Primary Key)
you could just do three (or N) SELECT statements to guarantee the order.

SELECT * FROM NEWFOLDER WHERE ID = 3;
SELECT * FROM NEWFOLDER WHERE ID = 1;
SELECT * FROM NEWFOLDER WHERE ID = 2;

This is NOT efficient, but it is what transaction processing systems do all
day with randomly arriving known customers.

If you need the results combined in one data structure (for all the values
of ID) you could make this more elaborate with a UNION query
or you could assemble the data in the language that you are calling SQL
from (assuming you are not using the command line interface). If you are
using the command line interface you could redirect to a file and append
(">" and ">>").

Jim Callahan
Callahan Data Science LLC
Orlando, FL


Virus-free.
www.avast.com

<#m_-7888910753152976491_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, Mar 9, 2018 at 8:14 AM, Hegde, Deepakakumar (D.) <
deep...@allgosystems.com> wrote:

> Hi All,
>
>
> We have a problem as below:
>
>
> we have created a table as below:
>
> CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;
>
> We have inserted 5 entry to this table, and ID will be from 1 to 5 as below
>
> ID   NAME
> 1 ABC
>
> 2 AAA
>
> 3 CBA
>
> 4 BAC
>
> 5 BBB
>
>
> We execute following select statetment:
>
>
> SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);
>
>
> output for above is:
>
>
> ID   NAME
>
> 1 ABC
>
> 2 AAA
>
> 3 CBA
>
>
> It seems by default sqlite is getting the entry in the order of primary
> key or rowid.
>
>
> So for us expected output is:
>
> ID   NAME
>
> 3 CBA
>
> 1 ABC
>
> 2 AAA
>
>
> Is there anyway to do this without adding a new column? with the same
> table?
>
> we need a way where by we can get the entry as we given in "where" "in"
> clause
>
>
> Thanks and Regards
>
> Deepak
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Richard Hipp]

On 3/9/18, Hegde, Deepakakumar (D.)  wrote:
>
> So for us expected output is:

If your query does not have an ORDER BY clause, then SQLite (and every
other SQL database engine) is free to return the result rows in any
order it wants.

At this point in history, SQLite happens, by chance, to return the
rows in rowid order for your particular query.  But that might change
with the next release.  Or it might change with shifts in the jet
stream.  You never know.  The point is that you must never depend on a
particular row order from a SELECT statement that lacks an ORDER BY
clause.

If you need to output rows in a particular order, then please devise
an ORDER BY clause that expresses that ordering.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

[petern]

WITH "cte" is a table only WRT the UPDATE's RHS input space.  eg:

CREATE TABLE t AS SELECT (column1)i,(NULL)a FROM (VALUES (1),(2),(3));

WITH cte(i,a) AS (VALUES (1,10),(2,20)) UPDATE t SET a=(SELECT a FROM cte
WHERE i=t.i);

SELECT * FROM t;
i,a
1,10
2,20
3,

[FYI.  WITH ... INSERT/UPDATE/DELETE is not supported in a trigger body.]


On Thu, Mar 8, 2018 at 8:09 PM, sanhua.zh  wrote:

> I find that a UPDATE statement with WITH CLAUSE always fails, although I
> use the syntax as SQLite syntax suggested.
> Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE.
> They all run in a SELECT statement.
>
>
> Here is the sample SQL I tried:
> ```
> CREATE TABLE t(i INTEGER);
> INSERT INTO t VALUES(1);
> WITH cte AS(SELECT i FROM t)
> UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
> ```
>
>
> Note that the sample WITH CLAUSE make no sense. It's just for testing. But
> still failed.
> Did I use it in a wrong way? What's the suggesting rule?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[R Smith]

On 2018/03/09 3:14 PM, Hegde, Deepakakumar (D.) wrote:

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


Yes, as it should - A "set" has no inherent order to it - that IN 
statement is equivalent to saying:
Show the name if it has an ID found in this bag of goodies: ( car, 2, 
lemon, three, 3, tree, 1 ) which is exactly the same as this bag: ( 3, 
1, lemon, tree, 2, car, three  ) - the order does not matter.
What you see is the order in which the items get selected to check if 
they have an ID in the bag - that is usually (but not always) the 
primary key order.


If you need anything to be ordered, you have to specify the order.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?


Usually specifying the order is easy, but you seem to want to make up 
the order as you go - this can still be done, but is more technical. 
Here is an example joining to a subquery that sets up sort and sid 
fields so you can specify both the ID to join and the sort order in 
which it should be produced. The part after the VALUES is all you need 
to adjust.


SELECT *
  FROM NEWFOLDER
  JOIN (SELECT -1 sort, -1 sid UNION ALL  VALUES  (1,3),  (2,1), (3,2) 
)  AS X ON ID = X.sid

 ORDER BY X.sort

Here it is working with Aliasing to produce specific columns only:


  -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

INSERT INTO NEWFOLDER(ID, NAME) VALUES
 (1, 'ABC')
,(2, 'AAA')
,(3, 'CBA')
,(4, 'BAC')
,(5, 'BBB')
;

SELECT A.*
  FROM NEWFOLDER AS A
  JOIN (SELECT -1 sort, -1 sid UNION ALL VALUES (1,3), (2,1), (3,2)) AS 
X ON A.ID = X.sid

 ORDER BY X.sort


  --  ID | NAME
  -- --- | 
  --  3  |  CBA
  --  1  |  ABC
  --  2  |  AAA






we need a way where by we can get the entry as we given in "where" "in" clause


That is impossible.




Thanks and Regards

Deepak


Cheers,
Ryan

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

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Keith Medcalf]

Sets of things inherently have no order.  Since you have not specified an order 
(as in an order by clause), any ordering you perceive is simply a figment of 
your imagination and does not, in reality, exist.

You can always add another column and put your order in it so that you can sort 
by that column, or retrieve the rows one at a time so that your application can 
deal with them sequentially.  However, set-based data management systems do not 
have any inherent order in sets of rows processed ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar
>(D.)
>Sent: Friday, 9 March, 2018 06:15
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] How to get the entry from sqlite db without primary
>key or rowid order?
>
>Hi All,
>
>
>We have a problem as below:
>
>
>we have created a table as below:
>
>CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;
>
>We have inserted 5 entry to this table, and ID will be from 1 to 5 as
>below
>
>ID   NAME
>1 ABC
>
>2 AAA
>
>3 CBA
>
>4 BAC
>
>5 BBB
>
>
>We execute following select statetment:
>
>
>SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);
>
>
>output for above is:
>
>
>ID   NAME
>
>1 ABC
>
>2 AAA
>
>3 CBA
>
>
>It seems by default sqlite is getting the entry in the order of
>primary key or rowid.
>
>
>So for us expected output is:
>
>ID   NAME
>
>3 CBA
>
>1 ABC
>
>2 AAA
>
>
>Is there anyway to do this without adding a new column? with the same
>table?
>
>we need a way where by we can get the entry as we given in "where"
>"in" clause
>
>
>Thanks and Regards
>
>Deepak
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Andy Ling]

Whoops, your right. I should have tested. I am more used to MySQL and assumed 
field was more "standard".

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Fri 09 March 2018 13:40
To: SQLite mailing list
Subject: [External] Re: [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Don’t think there is a FIELD function in sqlite Andy (MySQL has one).



with cte(ID) as (values (3),(1),(2))

select * from cte inner join Array using (ID);



will return them in the required order. That is to say, if you have a table 
where the records are ordered 3, 1, 2 then you can get the required result. As 
to whether that’s any use to you depends on how you’re creating the (3, 1, 2) 
list.




From: sqlite-users  on behalf of 
Andy Ling 
Sent: Friday, March 9, 2018 1:30:25 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] How to get the entry from sqlite db without primary key 
or rowid order?

First, you cannot rely on the order of the rows unless you specify it. So it is 
"just luck" that they are in ID order.

To get want you want you must specify an order and something like this will do 
what you want..

SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2);

So you need to put the same IDs in the FIELD as you have in the IN.

HTH

Andy Ling

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: sqlite-users@mailinglists.sqlite.org
Subject: [External] [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?

we need a way where by we can get the entry as we given in "where" "in" clause


Thanks and Regards

Deepak


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[x]

Don’t think there is a FIELD function in sqlite Andy (MySQL has one).



with cte(ID) as (values (3),(1),(2))

select * from cte inner join Array using (ID);



will return them in the required order. That is to say, if you have a table 
where the records are ordered 3, 1, 2 then you can get the required result. As 
to whether that’s any use to you depends on how you’re creating the (3, 1, 2) 
list.




From: sqlite-users  on behalf of 
Andy Ling 
Sent: Friday, March 9, 2018 1:30:25 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] How to get the entry from sqlite db without primary key 
or rowid order?

First, you cannot rely on the order of the rows unless you specify it. So it is 
"just luck" that they are in ID order.

To get want you want you must specify an order and something like this will do 
what you want..

SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2);

So you need to put the same IDs in the FIELD as you have in the IN.

HTH

Andy Ling

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: sqlite-users@mailinglists.sqlite.org
Subject: [External] [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?

we need a way where by we can get the entry as we given in "where" "in" clause


Thanks and Regards

Deepak


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Andy Ling]

First, you cannot rely on the order of the rows unless you specify it. So it is 
"just luck" that they are in ID order.

To get want you want you must specify an order and something like this will do 
what you want..

SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2);

So you need to put the same IDs in the FIELD as you have in the IN.

HTH

Andy Ling

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: sqlite-users@mailinglists.sqlite.org
Subject: [External] [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?

we need a way where by we can get the entry as we given in "where" "in" clause


Thanks and Regards

Deepak


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

[sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Hegde, Deepakakumar (D.)]

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?

we need a way where by we can get the entry as we given in "where" "in" clause


Thanks and Regards

Deepak


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

Re: [sqlite] Selecting a non-unique column name in a WHERE clause

[Clemens Ladisch]

Balaji Ramanathan wrote:
> I have some columns repeated multiple times, and I find that
> some of the repeated columns have postfixes like ":52449101",
> ":3016716403", ":197485499", ":3551085770", etc.  Where do these postfixes
> come from?

SQLite adds a number to make the name unique.  After three tries,
to speed up the search, it uses random numbers instead.

> can I predict what the column names in the resulting output will be?

No.  Igor already told you:

> Don't use "select *" in the view. Explicitly select columns you need,
> assign aliases to them as needed.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use WITH CLAUSE in a UPDATE statement?

[R Smith]

On 2018/03/09 8:43 AM, sanhua.zh wrote:

I find that a UPDATE statement with WITH CLAUSE always fails, although I use 
the syntax as SQLite syntax suggested.
Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They 
all run in a SELECT statement.


Here is the sample SQL I tried:
```
CREATE TABLE t(i INTEGER);
INSERT INTO t VALUES(1);
WITH cte AS(SELECT i FROM t)
UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"


You cannot update the CTE table, it doesn't exist anywhere and the 
example certainly shouldn't suggest it like that either -  did you mean 
to update table t?  Where did you find an example like that?


A CTE is a temporary VIEW to help you formulate the result of a select 
in a specific way. Most of what can be accomplished with a CTE can also 
be done with a sub-query, except it has some really nice other features, 
but you can always think of it as a kind of sub-query. Just as it makes 
no sense to try and UPDATE a sub-query, it makes no sense to update a 
CTE, it's a view, not an actual data-wielding table.


This is an example of a CTE used in an UPDATE query that simply adds 100 
to the IDs, probably not something you'd need to do, but it illustrates 
the usage:


Imagine There is a Table "t" which has an integer ID column.

WITH CT(oldID, newID) AS (
    SELECT ID, ID+100 FROM t
)
UPDATE t SET ID = (SELECT newID FROM CT WHERE oldID = ID);

Note that the only table that can be updated is t since it really 
exists, CT doesn't really exist, it's only a view of t.


Hope that makes it more clear!
Ryan

PS: Here you can learn more accurately about CTEs in SQLite: 
https://sqlite.org/lang_with.html


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