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
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
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.
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
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)
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
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
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
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
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
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
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
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
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
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
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
, 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
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 >=
-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
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 >=
>
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 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
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
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
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
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
> --- -- ---
>
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
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
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
>
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
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
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
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:
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
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
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
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
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
>
> 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
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
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
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
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
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
45 matches
Mail list logo