Re: [sqlite] UPDATE question

p 2013 17:45:59 -0400 (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

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

On 07-09-2013 03:27, James K. Lowden wrote: On Fri, 6 Sep 2013 11:07:27 -0400 Richard Hipp wrote: The effect of early row updates might be visible in later row updates if you contrive a *sufficiently* complex example. But you really have to go out of your way to do that.

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

workaround for your problem : create table t1 (pk integer primary key, name text, seq integer) ; create unique index idxt1 on t1 (name,seq) ; insert into t1 values (1, 'blue', 1) ; insert into t1 values (2, 'blue', 2) ; insert into t1 values (3, 'blue', 3) ; insert into t1 values (4, 'blue', 4)

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

On Fri, 6 Sep 2013 11:07:27 -0400 Richard Hipp wrote: > The effect of early row updates might be visible in later row updates > if you contrive a sufficiently complex example. But you really have > to go out of your way to do that. sqlite> create table i ( i int primary key

Re: [sqlite] UPDATE question

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 Haworth Sent: Thursday, September 05, 2

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE Question

General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] UPDATE question > Message-ID: > <e7544051a0971a48b80ab118ac58918e05fb7da...@mbx03.exg5.exghost.com > > > Content-Type: text/plain; charset="us-ascii" > > No one

Re: [sqlite] UPDATE question

On 06.09.2013 18:30, Dominique Devienne wrote: 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

Re: [sqlite] UPDATE question

On 06.09.2013 18:30, Dominique Devienne wrote: 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

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

Discussion of SQLite Database Subject: Re: [sqlite] UPDATE question On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne <ddevie...@gmail.com>wrote: > Myself, if I'm "thinking in sets", all implementation details aside, > the UPDATE statement looks fine and correct, and I'd h

Re: [sqlite] UPDATE question

, 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 <ddevie...@gmail.com>wrote: Myself, if I'm "thinking in sets", all implementation details aside, the UPDATE statement looks fine and

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

-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 <p...@lcsql.com> wrote: > I hav

Re: [sqlite] UPDATE question

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

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 SQLite to > support it. > > But I'm just waiting to read Dr. Hipp's own read

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

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 the intended r

Re: [sqlite] UPDATE question

s-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 <jklow...@schemamania.org>wrot

Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote: > Here's how Marc L. Allen's query should work (different DBMS): > > $ bsqldb < /tmp/sql >PKey Name Sequence > --- -- --- >

Re: [sqlite] UPDATE question

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

Re: [sqlite] UPDATE question

On Thu, 5 Sep 2013 19:53:15 +0100 Simon Slavin wrote: > 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

Re: [sqlite] UPDATE question

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 >

Re: [sqlite] UPDATE question

D 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) str

Re: [sqlite] UPDATE question

PM 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 exam

[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, if the existing rows are: Name Sequence

Re: [sqlite] UPDATE question

Hi, Barry, Regarding: "Could you please explain me why the indexing is so important? (for future reference)." If you're asking just in *general* why indexes can speed up searching a database table, you might want to look at most any sql tutorial, or resources such as:

Re: [sqlite] UPDATE question

Igor Tandetnik wrote: > > Barry1337 wrote: >> So I need, for every record in STAYSPEC, to find another record in >> STAYSPEC >> that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I >> want to replace the date_out from STAYSPEC with that date (in

Re: [sqlite] UPDATE question

qlite.org] On Behalf Of Barry1337 Sent: Tuesday, October 18, 2011 6:04 AM To: sqlite-users@sqlite.org Subject: [sqlite] UPDATE question I have the following query: Code: UPDATE STAYSPEC SET date_out = CASE WHEN EXISTS (SELECT * FROM STAYSPEC AS STAYSPEC2 WHERE (STAYSPEC2.STAYNU

Re: [sqlite] UPDATE question

Barry1337 wrote: > So I need, for every record in STAYSPEC, to find another record in STAYSPEC > that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I > want to replace the date_out from STAYSPEC with that date (in text format). > > If such a record

[sqlite] UPDATE question

I have the following query: Code: UPDATE STAYSPEC SET date_out = CASE WHEN EXISTS (SELECT * FROM STAYSPEC AS STAYSPEC2 WHERE (STAYSPEC2.STAYNUM = STAYSPEC.STAYNUM) AND (STAYSPEC2.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) THEN (SELECT date_in FROM STAYSPEC AS

Re: [sqlite] Update question

Jon Dixon <[EMAIL PROTECTED]> wrote: > I am updating how I handle dates/durations in a database of mine and > I would like to come up with an SQL command to make the change for > me. > > The old format had a field Departure (-MM-DD) and a field Nights > (an integer), where the new format >

Re: [sqlite] Update question

> My stab at an SQL statement to pull this change off is as follows: > UPDATE > Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT > pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights > != '') Why do you think you need the sub-select? Assuming that they

[sqlite] Update question

Hello all, I am updating how I handle dates/durations in a database of mine and I would like to come up with an SQL command to make the change for me. The old format had a field Departure (-MM-DD) and a field Nights (an integer), where the new format still uses Departure but adds Return

Re: [sqlite] Update question

ubject: [sqlite] Update question Hello to all I want to know if sqlite supports updates to more than a table, as mysql allows Ex: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; Thanks

RE: [sqlite] Update question

is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Cesar Rodas [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 07, 2007 2:53 PM To: sqlite-users@sqlite.org Subject: [sqlite] Update question Hello to all I want to know if sqlite supports

[sqlite] Update question

Hello to all I want to know if sqlite supports updates to more than a table, as mysql allows Ex: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; Thanks to all

[sqlite] sqlite update question

I seem to be having a problem with updates to a table. I am using SQLITE_VERSION "3.3.8", statically compiled on Win32 with VC++ 6. I have two sqlite3_stmt* instances in the same process. The first is allocated by a call to sqlite3_prepare() with a SELECT statement. The second is allocated with