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

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.

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

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

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

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

[sqlite] Site error

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.

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

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"

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

> 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

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

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

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

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.

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

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:

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

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,

Re: [sqlite] Help getting started

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

Re: [sqlite] Help getting started

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

Re: [sqlite] Help getting started

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,

Re: [sqlite] Help getting started

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

[sqlite] Help getting started

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

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

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.

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

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

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

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

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

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 ...

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

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

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

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

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

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:

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

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

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

[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

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

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

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

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