Re: [sqlite] Updating a table from itself

On 6/8/2013 2:51 PM, Dave Wellman wrote: update t1 from (select c1,c2 from t1) as dt1 set c2 = dt1.c2 where t1.c1 = dt1.c2 - 1; update t1 set c2 = coalesce((select c2 from t1 dt1 where t1.c1 = dt1.c2 - 1), c2); -- Igor Tandetnik ___ sqlite-users

[sqlite] Updating a table from itself

Hi, Can someone please point me in the direction of the SQLite syntax structure for updating a table by joining to itself. I need to update one row with the contents of another. Based on my normal database (Teradata) I was looking for something like update t1 from (select c1,c2 from t1)

Re: [sqlite] Transaction

On 8 Jun 2013, at 5:27pm, RSmith wrote: > Thank you very much for taking the time - this did answer my question. You're welcome. > If I may one more question related to this: Let's say I am using > transactions as validation stops on a long list of inputs - So > that the

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

I did (select * from qqq) but not from the sqlite3 shell. I can see that via the shell you get the output as you mention. My output was via a VB wrapper and I take it that the zero showing instead of FIELD1 has to do with that. Will have a look at that and thanks for alerting me to this

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

I don't think you did because if you had then what you would have seen would be as follows: SQLite version 3.7.17 2013-06-05 16:17:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT); sqlite> .mode csv sqlite>

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Not sure what you are getting at now. > Select * from QQQ That is exactly what I did. It is all cleared up now in any case. RBS On Sat, Jun 8, 2013 at 4:56 PM, Keith Medcalf wrote: > > Table will then be like this: > > > > FIELD1 FIELD2 > >

Re: [sqlite] Transaction

Apologies, this went to wrong mailbox first... -- Thank you very much for taking the time - this did answer my question. If I may one more question related to this: Let's say I am using transactions as validation stops on a long list of

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

> Table will then be like this: > > FIELD1 FIELD2 > - > 0 FIELD2 > 1 ABC > 2 BCD > 3 CDE How did you arrive at this -- the table looks NOTHING like what you (I can only assume) you ASSUMED it looks like. Instead of assuming what the table looks like, why not

Re: [sqlite] Transaction

On 8 Jun 2013, at 3:35pm, RSmith wrote: > A) so to have: > BEGIN > SAVEPOINT A > ... > RELEASE A > SAVEPOINT B > ... > RELEASE B > ... > COMMIT; > > B) or to have: > BEGIN > SAVEPOINT A > ... >SAVEPOINT B > ... >RELEASE B > ... > RELEASE A > COMMIT;

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Yes, thanks, all clear now. RBS On Sat, Jun 8, 2013 at 4:03 PM, Michael Black wrote: > Or to get all the non-integer records. > > > select * from qqq where typeof(field1) <> 'integer'; > > Mike > > -Original Message- > From: sqlite-users-boun...@sqlite.org >

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Or to get all the non-integer records. select * from qqq where typeof(field1) <> 'integer'; Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, June 08, 2013 9:58 AM To: rsm...@rsweb.co.za;

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Aaah, OK, that answers my question! This is something I hadn't realised at all and good to know that one. Thanks for clearing this up. RBS On Sat, Jun 8, 2013 at 3:48 PM, RSmith wrote: > Yes, FIELD1 values are formatted int he output to be displayed as 0 since > it is a

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Whatever it gets turned into, my question is how I can select that record with a where clause specifying field1? Surely, there must be some way. RBS On Sat, Jun 8, 2013 at 3:48 PM, Michael Black wrote: > What makes you think field1 gets turned into a zero? Fields are

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Yes, FIELD1 values are formatted int he output to be displayed as 0 since it is a INTEGER field, but the real value of FIELD1 is "FIELD1" for the 0th record, since that is what was imported from the CSV. The formatted value is not always the same as the real value. try: select * from qqq where

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

What makes you think field1 gets turned into a zero? Fields are really typeless in SQLite3 Your .dump should look like this: SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2]

[sqlite] Strange table behaviour after text import with sqlite3.exe

Have table defined like this: CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) Table is empty, so has no records. Then I import a text file with this data: FIELD1,FIELD2 1,ABC 2,BCD 3,CDE This is via sqlite3.exe with: .mode csv .import textfilename QQQ Table will then be like this: FIELD1

Re: [sqlite] Transaction

Ugh, of course, stupid mistake in explanation - Of course it must be SAVEPOINT, not again BEGIN. A) so to have: BEGIN SAVEPOINT A ... RELEASE A SAVEPOINT B ... RELEASE B ... COMMIT; B) or to have: BEGIN SAVEPOINT A ... SAVEPOINT B ... RELEASE B ... RELEASE A

Re: [sqlite] Transaction

On 6/8/2013 10:17 AM, RSmith wrote: Could someone please shortly discuss performance considerations for having nested Transactions vs. sequential transactions in SQLite. There ain't no such thing as a nested transaction. The second BEGIN statement will simply fail. -- Igor Tandetnik

[sqlite] Transaction

Hi all, I've read some of the standard help on sqlite.org but have not studied the code at all, so turning to the experts for some advice (there is no urgency, I would just really like some insight). Could someone please shortly discuss performance considerations for having nested

[sqlite] "unable to open database file" after upgrade from 1.0.85 to 86

I made no changes to my application other than updating the sqlite control via Nuget. Now when my application runs it throws the following error: SqliteException (0x80004005): Unable to open database file. If I run using 1.0.85 I get no error. Any help would be greatly appreciated. Thanks,