Hugues Bruant wrote on Wednesday, September 23, 2015 2:06 AM
> > > in some cases the SIndex captured inside the first lambda (UPDATE
> > > statement) appeared to be null even though it wasn't null in the
> > > enclosing scope (setVersion_)
> >
> > Interesting (and disturbing) result. Is this with O
On 23 September 2015 at 12:32, Hugues Bruant wrote:
> On Wed, Sep 23, 2015 at 12:00 AM, Rowan Worth wrote:
>
> > Has many possible explanations.
>
> I can't think of any that is consistent with the Java specification.
>
Yeah fair enough, SIndex looks watertight. It's still possible for some
oth
> SIndex sidx is just a boxed immutable integer.
If it were me I'd be reviewing this assumption very carefully. The sequence
of events:
1. SIndex.getInt() returns 0
2. SIndex.toString() returns "1"
3. SIndex.getInt() returns 1
Has many possible explanations. You could also try 'final int sid =
s
> > in some cases the SIndex captured inside the first lambda
> > (UPDATE statement) appeared to be null even though it wasn't null in the
> > enclosing scope (setVersion_)
>
> Interesting (and disturbing) result. Is this with Oracle's java compiler?
>
Compiler is Oracle JDK 8u40-b25 on OSX
Runtime
On Wed, Sep 23, 2015 at 12:00 AM, Rowan Worth wrote:
> > SIndex sidx is just a boxed immutable integer.
>
> If it were me I'd be reviewing this assumption very carefully. The sequence
> of events:
>
public class SIndex {
private final int _i;
public SIndex(int i) {
_i = i;
}
public int getInt(
On 22 Sep 2015, at 5:40am, Hugues Bruant wrote:
> Forgot to include the db in the previous email.
You can't attach files to messages in this list. If it's important, please
post the file somewhere and send us -- or perhaps just a couple of people -- a
URL.
Simon.
On 22 Sep 2015, at 5:44pm, Hugues Bruant wrote:
> Memory corruption seems unlikely: I would expect more errors to follow once
> some piece of memory is corrupted. Valgrind is unfortunately not very
> useful in this case as its OS X support is limited and it doesn't like JIT
> very much.
If a JV
Not important.
On Tue, Sep 22, 2015 at 6:00 PM, Simon Slavin wrote:
>
> On 22 Sep 2015, at 5:40am, Hugues Bruant wrote:
>
> > Forgot to include the db in the previous email.
>
> You can't attach files to messages in this list. If it's important,
> please post the file somewhere and send us --
On 2015-09-22 03:58 PM, Hugues Bruant wrote:
> On Tue, Sep 22, 2015 at 6:22 AM, Richard Hipp wrote:
>
>> On 9/22/15, Hugues Bruant wrote:
If you can capture a malfunctioning trace, and send in the database
file and the SQL statement that is running, that should allow us to
locali
R.Smith wrote on Tuesday, September 22, 2015 10:08 AM
>
> On 2015-09-22 03:58 PM, Hugues Bruant wrote:
> > SIndex sidx is just a boxed immutable integer. Its value is bound to
> > the UPDATE and the trace shows it to be 0 But the log line printed
> > when the number of rows updated is zero clearly
On 2015-09-22 06:37 AM, Hugues Bruant wrote:
>> If you can capture a malfunctioning trace, and send in the database
>> file and the SQL statement that is running, that should allow us to
>> localize the problem.
>>
> Trace for the failing UPDATE:
>
> cv_s=1
> cv_o=b8b9f4...
> cv_t=28 -> 29
>
> SQ
>
> That does look a bit like magic - and as is 99.9% the case when
> inexplicable magic happens, memory is getting corrupt or overwritten. You
> are obviously prudent about this, but is there no clue when running under
> Valgrind or similar for OSX? Nothing out of the ordinary or warnings?
>
Memor
On Tue, Sep 22, 2015 at 6:22 AM, Richard Hipp wrote:
> On 9/22/15, Hugues Bruant wrote:
> >>
> >> If you can capture a malfunctioning trace, and send in the database
> >> file and the SQL statement that is running, that should allow us to
> >> localize the problem.
> >>
> >
> > Trace for the fai
On 9/22/15, Hugues Bruant wrote:
>>
>> If you can capture a malfunctioning trace, and send in the database
>> file and the SQL statement that is running, that should allow us to
>> localize the problem.
>>
>
> Trace for the failing UPDATE:
>
> cv_s=1
The trace shows that you have this value set t
Forgot to include the db in the previous email.
On Tue, Sep 22, 2015 at 12:37 AM, Hugues Bruant wrote:
> If you can capture a malfunctioning trace, and send in the database
>> file and the SQL statement that is running, that should allow us to
>> localize the problem.
>>
>
> Trace for the failin
>
> If you can capture a malfunctioning trace, and send in the database
> file and the SQL statement that is running, that should allow us to
> localize the problem.
>
Trace for the failing UPDATE:
cv_s=1
cv_o=b8b9f4...
cv_t=28 -> 29
SQL: [update cv set cv_t=? where cv_s=? and cv_o=?]
VDBE Progr
On 21 Sep 2015, at 10:44pm, Hugues Bruant wrote:
> We've only observed this on OS X so far, with both 3.8.7 and 3.8.11.1. The
> new value is always exactly the old value +1 when the statement fails.
Can you reproduce this problem with the SQLite shell tool included with Mac OS
X ? You should
On Mon, 21 Sep 2015 17:44:13 -0400
Hugues Bruant wrote:
> UPDATE cv SET cv_t=? where cv_s=? and cv_o=?;
>
> Most of the time the row is updated as expected but in some rare cases
> we've seen this statement fail silently, as in:
> - the row exists
> - the row it is not updated
> - step ret
On 9/21/15, Hugues Bruant wrote:
> On Mon, Sep 21, 2015 at 8:43 PM, Richard Hipp wrote:
>
>> On 9/21/15, Hugues Bruant wrote:
>> >
>> > I would be happy to build and test a patched sqlite with extra logging,
>> > some sort of vdbe execution tracing
>>
>> Compile with -DSQLITE_DEBUG and then use
On Mon, Sep 21, 2015 at 8:43 PM, Richard Hipp wrote:
> On 9/21/15, Hugues Bruant wrote:
> >
> > I would be happy to build and test a patched sqlite with extra logging,
> > some sort of vdbe execution tracing
>
> Compile with -DSQLITE_DEBUG and then use "PRAGMA vdbe_debug=ON;"
> before the offend
On 9/21/15, Hugues Bruant wrote:
>
> I would be happy to build and test a patched sqlite with extra logging,
> some sort of vdbe execution tracing
Compile with -DSQLITE_DEBUG and then use "PRAGMA vdbe_debug=ON;"
before the offending UPDATE and turn if off afterwards.
--
D. Richard Hipp
drh at sq
On Mon, Sep 21, 2015 at 8:23 PM, Richard Hipp wrote:
> On 9/21/15, Hugues Bruant wrote:
> > On Mon, Sep 21, 2015 at 6:49 PM, Richard Hipp wrote:
> >
> >> On 9/21/15, Hugues Bruant wrote:
> >> > Table schema:
> >> >
> >> > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t
> >> >
On 9/21/15, Hugues Bruant wrote:
> On Mon, Sep 21, 2015 at 6:49 PM, Richard Hipp wrote:
>
>> On 9/21/15, Hugues Bruant wrote:
>> > Table schema:
>> >
>> > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t
>> > integer
>> > not null, primary key(cv_s, cv_o));
>> >
>> > Prepared sta
On Mon, Sep 21, 2015 at 6:49 PM, Richard Hipp wrote:
> On 9/21/15, Hugues Bruant wrote:
> > Table schema:
> >
> > CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t integer
> > not null, primary key(cv_s, cv_o));
> >
> > Prepared statement:
> >
> > UPDATE cv SET cv_t=? where cv_s=?
On 9/21/15, Hugues Bruant wrote:
> Table schema:
>
> CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t integer
> not null, primary key(cv_s, cv_o));
>
> Prepared statement:
>
> UPDATE cv SET cv_t=? where cv_s=? and cv_o=?;
My guess is that the WHERE clause matches no rows. So it i
>
> Ideally, open the database which exhibits the problem and first execute
>
> PRAGMA integrity_check;
>
Integrity check does not report any issue.
> If this fails to show any problems run your UPDATE command, repeatedly if
> necessary, and see if you can make the shell tool do anything that su
Table schema:
CREATE TABLE cv (cv_s integer not null, cv_o blob not null, cv_t integer
not null, primary key(cv_s, cv_o));
Prepared statement:
UPDATE cv SET cv_t=? where cv_s=? and cv_o=?;
Most of the time the row is updated as expected but in some rare cases
we've seen this statement fail sile
uot;myTable" VALUES(8655,8655);
> INSERT INTO "myTable" VALUES(7966,7966);
> INSERT INTO "myTable" VALUES(1647,1647);
> INSERT INTO "myTable" VALUES(7780,7780);
> INSERT INTO "myTable" VALUES(9088,9088);
>
> In the following, ignore the
47);
INSERT INTO "myTable" VALUES(7780,7780);
INSERT INTO "myTable" VALUES(9088,9088);
In the following, ignore the indented rows. Those are me putting the original
values back where they belong after each test.
sqlite> UPDATE myTable SET x= WHERE x<100;
Run Time: r
Hi all,
When I do an update in a huge table, should it write it like this
UPDATE T1 SET F1=replace(F1, 'x', 'y') WHERE F1 LIKE '%x%';
or without where clause like this
UPDATE T1 SET F1=replace(F1, 'x', 'y');
T1 has an index on F1, which is a TEXT field.
thanks
gert
On 24 May 2015, at 4:16pm, Gert Van Assche wrote:
> When I do an update in a huge table, should it write it like this
>UPDATE T1 SET F1=replace(F1, 'x', 'y') WHERE F1 LIKE '%x%';
> or without where clause like this
>UPDATE T1 SET F1=replace(F1, 'x', 'y');
>
> T1 has an index on
e> insert into x select random() from n where value between 1 and 10;
sqlite> .eqp on
sqlite> .timer on
sqlite> update x set x=x where cast(x/2 as integer)*2=x;
--EQP-- 0,0,0,SCAN TABLE x
Run Time: real 0.087 user 0.078125 sys 0.00
sqlite> update x set x=x;
--EQP-- 0,0,0,SCA
--
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Gert Van Assche
> Sent: Sunday, 24 May, 2015 09:17
> To: General Discussion of SQLite Database
> Subject: [sqlite] Update with out without WHERE?
>
> Hi
Thanks to all for explaining my confusion. The printf solution seems like
the best way to handle this since I don't need to worry about how many
decimal places are in the number.
On 2015-03-12 01:27 AM, Peter Haworth wrote:
> I have a table, Transactions, with a column , Value, of type NUMERIC. The
> Value column is supposed to have 2 decimal places in all rows but some have
> only one.
SQLite has no formatting inherent to the column value, there is no such
thing as "s
Hi Peter,
From https://www.sqlite.org/datatype3.html:
"When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if
such conversion is lossless and reversible"; basically any fraction you
insert into a NUMERIC colu
I have a table, Transactions, with a column , Value, of type NUMERIC. The
Value column is supposed to have 2 decimal places in all rows but some have
only one.
To correct this I issued the following command:
UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.'
No errors on execut
mbined: nothing works and no one knows why.
>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of Peter Haworth
>Sent: Wednesday, 11 March, 2015 17:28
>To: sqlite-users at mailingl
On 3/5/2015 11:58 PM, Igor Tandetnik wrote:
> If the latter is OK, then you can have a single trigger doing something
> like this:
>
> insert or replace into Clean(X, Y, Z)
> select
> case when new.X = ifnull(c.X, d.X) then c.X else new.X end,
> case when new.Y = ifnull(c.Y, d.Y) then c.Y e
On Mar 6, 2015 7:33 AM, "Igor Tandetnik" wrote:
A bunch of good stuff snipped...
Thanks for the alternative suggestions. In thinking it over since my
message, I've decided the multiple trigger approach isn't at all bad. A
little verbose, but each column of the view has its own callback and SQLite
On 3/5/2015 9:56 PM, Scott Robison wrote:
> Finally, my question: Is there some sort of syntax that I'm missing that
> would "simplify" my schema with a single update trigger, or is this the
> proper way to "update" individual columns of a view?
Imagine that the "clean" table has null in column X
I have a use case to update a view, and wanted to make sure I'm not missing
anything obvious.
The basics of my schema are that I have a table from a third party that
might be updated at any time. A lot of the data in that table is garbage
though, and I want cleaner data, so I have a parallel table
thanks Simon
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Update-Statements-using-Sub-query-tp80175p80181.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http:
@gmail.com]
Gesendet: Dienstag, 20. Jänner 2015 14:10
An: sqlite-users@sqlite.org
Betreff: [sqlite] Update Statements using Sub query
I was wondering if anyone could help
I am trying to use Excel to create update statements based on certain criteria.
For example...this works
UPDATE CDR_Ad
014' );
sqlite>
sqlite> select column_name from p where provider='abc' and
version='2013-2014' and column_name='Date';
Date
sqlite>
sqlite> create table a( datetime text, time text );
sqlite> insert into a values( '',
I was wondering if anyone could help
I am trying to use Excel to create update statements based on certain
criteria.
For example...this works
UPDATE CDR_Adjusted SET "DateTime"=DATE||" "||TIME;
(This is what I need, a simple Concat
2013-10-11 7:59
But when I try to do this
UPDATE C
: [sqlite] UPDATE Help
On 15 Oct 2014, at 1:10am, Shantanu Namjoshi
wrote:
> ALTER TABLE dailydelete ADD COLUMN SMB varchar(11);
If you find yourself doing things like this your schema is messed up. SMB is
obviously data, not a column. Redesign your table so that HML, SMB, UMD and Rf
are val
SQLite Database
Subject: Re: [sqlite] UPDATE Help
On Tue, Oct 14, 2014 at 8:10 PM, Shantanu Namjoshi <
shantanu.namjo...@business.uconn.edu> wrote:
>
> UPDATE dailydelete
> SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE
> dailydelete.data = dailyfactors.Date),
On Tue, Oct 14, 2014 at 8:10 PM, Shantanu Namjoshi <
shantanu.namjo...@business.uconn.edu> wrote:
>
> UPDATE dailydelete
> SET SMB = (SELECT dailyfactors.SMB FROM dailyfactors WHERE
> dailydelete.data = dailyfactors.Date),
> SET Rf = (SELECT dailyfactors.Rf FROM dailyfactors WHERE dailydelete.data
On 15 Oct 2014, at 1:10am, Shantanu Namjoshi
wrote:
> ALTER TABLE dailydelete ADD COLUMN SMB varchar(11);
If you find yourself doing things like this your schema is messed up. SMB is
obviously data, not a column. Redesign your table so that HML, SMB, UMD and Rf
are values in a column, not
Hello,
I was writing for some help with a problem that has me stumped.
I have two tables in a DB. Table1 = dailydelete Table2 = dailyfactors.
dailyfactors is a list of dates and some corresponding information HML, SMB,
UMD and Rf for each of these dates
dates are NOT repeated in this table
dai
> On 23 Jun 2014, at 4:10pm, Carlos Ferreira wrote:
>
> To access Blobs I use the direct access BLOB api from SQLite.
>
> However, what is the best way to update a record that contains a BLOB, and
> the only goals is to update the BLOB...
>
> The BLOB may shrink or increase...and that is the
Hello,
One question.
To access Blobs I use the direct access BLOB api from SQLite.
However, what is the best way to update a record that contains a BLOB, and the
only goals is to update the BLOB...
The BLOB may shrink or increase...and that is the reason why I cannot use the
BLOB Write and..
On 10 Nov 2013, at 9:03pm, Ulrich Goebel wrote:
> I want to have a column 'last_changed' in a table, which shows the date of
> the last change (inser or update) of the row. I though the default value in
> the CREATE TABLE was a good idea:
>
> CREATE TABLE (
> id integer,
> name text,
> las
Hallo,
I want to have a column 'last_changed' in a table, which shows the date
of the last change (inser or update) of the row. I though the default
value in the CREATE TABLE was a good idea:
CREATE TABLE (
id integer,
name text,
last_changed text default current_date
)
That works for
James,
probably a view is not slower, but if you need this data several times...
I wanted to do an update because we are using this data several times in
the process, and I did not want to add the missing pieces each time.
gert
2013/11/7 James K. Lowden
> On Mon, 4 Nov 2013 13:01:37 +0100
>
On Mon, 4 Nov 2013 13:01:37 +0100
Gert Van Assche wrote:
> Thanks James -- the select query is something I could do, but the
> update one I could not get that one right.
> I was considering to create a new table based on the select query,
> but since the real data set is millions of records, an
Thanks James -- the select query is something I could do, but the update
one I could not get that one right.
I was considering to create a new table based on the select query, but
since the real data set is millions of records, an update was the best
solution.
As always, thanks for your help.
g
On Sat, 2 Nov 2013 18:06:30 +0100
Gert Van Assche wrote:
> CREATE TABLE T (N, V, G);
> INSERT INTO T VALUES('a', 1, 'x');
> INSERT INTO T VALUES('b', 3, 'x');
> INSERT INTO T VALUES('c', null, 'x');
> INSERT INTO T VALUES('d', 80, 'y');
> INSERT INTO T VALUES('e', null, 'y');
> INSERT INTO T VALU
Good question Gert. Good solution, Igor and I like Keith's formatting.
I thought the list might be interested in some of the statistical issues
involved in determining if this method of replacing null values is an
appropriate method for your data analysis and alternatives that are
available.
The
sqlite-users-
> >boun...@sqlite.org] On Behalf Of Gert Van Assche
> >Sent: Saturday, 2 November, 2013 11:07
> >To: sqlite-users
> >Subject: [sqlite] Update and GROUP BY
> >
> >All, I have this table:
> >
> >DROP TABLE T;
> >CREATE TABLE T (N, V
Igor, that is exactly what I need. I also understand how it is done now.
Thanks!
gert
2013/11/2 Igor Tandetnik
> On 11/2/2013 1:06 PM, Gert Van Assche wrote:
>
>> All, I have this table:
>>
>> DROP TABLE T;
>> CREATE TABLE T (N, V, G);
>> INSERT INTO T VALUES('a', 1, 'x');
>> INSERT INTO T VA
update the value of V in rows where V IS NULL ...
>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Gert Van Assche
>Sent: Saturday, 2 November, 2013 11:07
>To: sqlite-users
>Subject: [sqlite] Update and GROUP
On 11/2/2013 1:06 PM, Gert Van Assche wrote:
All, I have this table:
DROP TABLE T;
CREATE TABLE T (N, V, G);
INSERT INTO T VALUES('a', 1, 'x');
INSERT INTO T VALUES('b', 3, 'x');
INSERT INTO T VALUES('c', null, 'x');
INSERT INTO T VALUES('d', 80, 'y');
INSERT INTO T VALUES('e', null, 'y');
INSER
All, I have this table:
DROP TABLE T;
CREATE TABLE T (N, V, G);
INSERT INTO T VALUES('a', 1, 'x');
INSERT INTO T VALUES('b', 3, 'x');
INSERT INTO T VALUES('c', null, 'x');
INSERT INTO T VALUES('d', 80, 'y');
INSERT INTO T VALUES('e', null, 'y');
INSERT INTO T VALUES('f', 60, 'y');
INSERT INTO T VA
0 (EDT)
> From: j.merr...@enlyton.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] UPDATE question
> Message-ID: <1378503959.951430...@apps.rackspace.com>
> Content-Type: text/plain;charset=UTF-8
>
> I propose that you remove the unique index because SQLite does
Yes, thanks. I was mistaken.
On Sep 6, 2013, at 9:27 PM, "James K. Lowden" wrote:
> On Fri, 6 Sep 2013 07:56:53 -0500
> "Marc L. Allen" wrote:
>
>> I don't think it's a bug.
>
> It is a bug as long as the behavior is in exception to the
> documentation.
>
>> I don't believe there's any d
ate table i ( i int primary key );
sqlite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique
sqlite> drop table i;
sqlite> create table i ( i int primary key );
sqlite> insert into i values (2);
sqlite>
Further comment :
If you want to implement a sequence of records in a table you can do it
much faster with only on record to update when you insert a value in the
middle of this sequence. For this purpose you shouldn't use a pseudo
array but a single or double ended queue with only one trigger
t into t1 values (4, 'blue', 4) ;
insert into t1 values (5, 'blue', 5) ;
before updating your unique index just drop and recreate it !
Behaviour till now :
sqlite> select *from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5
sqlite> update t1 set seq=seq+1 where pk
On Fri, 6 Sep 2013 17:45:59 -0400 (EDT)
j.merr...@enlyton.com wrote:
> I propose that you remove the unique index because SQLite does not
> handle the update case the way you want.
The correct general approach, in light of observed behavior,
1. begin IMMEDIATE transaction
2. select rows into
lite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Fri, 6 Sep 2013 07:56:53 -0500
"Marc L. Allen" wrote:
> I don't think it's a bug.
It is a bug as long as the behavior is in exception to the
documentation.
> I don't believe there's any defined rule for how SQL should behave,
> is there?
Of course there is. Hundreds of pages describe
you wanted to, you could have your initial "open the database" code check
for duplicates across those columns (and that 1 is the lowest Sequence for each
Name) -- then at least you'd know that you'd had one of those bugs.
J. Merrill
-Original Message-
From: Peter H
On 6 Sep 2013, at 4:08pm, Simon Slavin wrote:
> Right. As I posted in my message that I had tested, this isn't being done
> correctly. A conflict isn't a conflict until the write, and the write
> doesn't happen until the COMMIT. Therefore conflict testing needs to happen
> at the commit, fo
On 9/6/2013 1:05 PM, ibrahim wrote:
Perhaps you should really try it out cause my suggestion is totally
different from yours it uses a temporary table it works.
create table t1 (pk integer primary key, name text, seq integer) ;
You missed the whole "Name/Sequence pair of columns is defined as
On 06.09.2013 20:52, Igor Tandetnik wrote:
On 9/6/2013 1:05 PM, ibrahim wrote:
Perhaps you should really try it out cause my suggestion is totally
different from yours it uses a temporary table it works.
create table t1 (pk integer primary key, name text, seq integer) ;
You missed the whole "
On 05.09.2013 20:20, Peter Haworth wrote:
I have a table with the following (simplified) structure
PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER
The Name/Sequence pair of columns is defined as UNIQUE
I need to insert a new row into a point between two sequence numbers. For
example, if the
st sequence number. EXPLAIN QUERY PLAN indicates that
the TestIndex index was used.
Pete
On Fri, Sep 6, 2013 at 9:00 AM, wrote:
> Message: 27
> Date: Fri, 6 Sep 2013 07:50:39 -0500
> From: "Marc L. Allen"
> To: General Discussion of SQLite Database
> Subject
t;blue", 2) ;
insert into t1 (pk, name, seq) values (3, "blue", 3) ;
insert into t1 (pk, name, seq) values (4, "blue", 4) ;
insert into t1 (pk, name, seq) values (5, "blue", 5) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5
sqlite
t;blue", 2) ;
insert into t1 (pk, name, seq) values (3, "blue", 3) ;
insert into t1 (pk, name, seq) values (4, "blue", 4) ;
insert into t1 (pk, name, seq) values (5, "blue", 5) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5
sqlite
On 05.09.2013 20:20, Peter Haworth wrote:
I have a table with the following (simplified) structure
PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER
The Name/Sequence pair of columns is defined as UNIQUE
I need to insert a new row into a point between two sequence numbers. For
example, if the
On Fri, Sep 6, 2013 at 5:41 PM, ibrahim wrote:
> You can let sqlite handle the creation of a temporary table by :
>
> update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3
> order by seq desc) ;
>
> afterwards you can insert.
Unless I'm misunderstanding you, I already tried tha
Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question
On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne wrote:
> Myself, if I'm "thinking in sets", all implementation details aside,
> the UPDATE statement looks fine and correct, and I'd have expected
> SQLi
, 2013 11:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question
On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne wrote:
Myself, if I'm "thinking in sets", all implementation details aside,
the UPDATE statement looks fine and correct, and I'd
On 6 Sep 2013, at 1:50pm, Marc L. Allen wrote:
> No one commented on my second thread (written after I actually understood the
> problem!).
>
> But, I proposed a two update sequence to do it.
>
> UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert
> AND Name = name_
qlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of kyan
Sent: Friday, September 06, 2013 10:41 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question
On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth wrote:
> I have a table with the f
On Fri, Sep 6, 2013 at 2:50 PM, Marc L. Allen
wrote:
> No one commented on my second thread (written after I actually understood
> the problem!).
>
> But, I proposed a two update sequence to do it.
>
> UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >=
> seq_to_insert AND Name = name_to
On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne wrote:
> Myself, if I'm "thinking in sets", all implementation details aside, the
> UPDATE statement looks fine and correct, and I'd have expected SQLite to
> support it.
>
> But I'm just waiting to read Dr. Hipp's own read on this now. --DD
I
On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth wrote:
> I have a table with the following (simplified) structure
>
> PKeyINTEGER PRIMARY KEY
> NameTEXT
> Sequence INTEGER
>
> The Name/Sequence pair of columns is defined as UNIQUE
>
> I need to insert a new row into a point between two sequence num
On 9/6/2013 8:50 AM, Marc L. Allen wrote:
But, I proposed a two update sequence to do it.
UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND
Name = name_to_insert
UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name =
name_to_insert
I've used this sy
Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question
By forcing the "physical order" of the rows to be reversed, the UPDATE succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document it
somehow).
This email and any attachments are only for use by
-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Dominique Devienne
Sent: Friday, September 06, 2013 3:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question
On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote:
> Here's how Ma
e3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table jkl (name text, seq number, unique (name, seq));
sqlite> insert into jkl values ('blue', 1), ('blue', 2), (
On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote:
> That's perfectly good SQL. SQLite is simply not executing the
> update atomically.
>
> Anyone tempted to protest may be forgetting "atomic" means more than
> "all or nothing". It also means the DBMS may execute the transaction
> however it
works:
sqlite> create table a (a int, b int check( a + b = 3));
sqlite> insert into a values (1, 2);
sqlite> select * from a;
a b
-- --
1 2
sqlite> update a set a = b, b = a;
sqlite> select * from a;
a b
--
On 5 Sep 2013, at 7:20pm, Peter Haworth wrote:
> That works fine but wondering if there might be a single UPDATE statement
> that could do this for me. I can use the WHERE clause to select sequence
> 3,4, and 5 but the UPDATE has to process the rows in descending sequence
> order to avoid UNIQ
0 AND Name =
name_to_insert
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question
I have a table with the following (simplified
To: sqlite-users
Subject: [sqlite] UPDATE question
I have a table with the following (simplified) structure
PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER
The Name/Sequence pair of columns is defined as UNIQUE
I need to insert a new row into a point between two sequence numbers. For
example,
I have a table with the following (simplified) structure
PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER
The Name/Sequence pair of columns is defined as UNIQUE
I need to insert a new row into a point between two sequence numbers. For
example, if the existing rows are:
Name Sequence
---
101 - 200 of 701 matches
Mail list logo
