Re: [sqlite] sqlite3_step to select and update the same table

[Stephen Chrzanowski]

Well, with your 5 gig table, the select statement needs to still compile a
set of results somehow, be it data or pointers to the data, and remember
which row has been read.  As I said, I've never looked or traced the code,
but something has be be created somewhere that says "This is the next
record".  If I update the CURRENT record, the order in which the records are
retrieved/stored shouldn't change.  Maybe if just doing a [select * from
MyTable] will yeild a simple pseudo thought of "Start from the first
physical record, then go to the next physical record when needed" but if I
change the statement to [select * from MyTable order by SortOrder] it can't
go by the first physical record in that table, of course, but some sort of
compiled list stating "The first record is physical row B, second is
physical row Z..." etc.

I'm not look'n to piss anyone off, of course, I'm just trying to figure out
how it works in the background.

On Mon, Nov 8, 2010 at 5:25 PM, Simon Slavin  wrote:

> >
> > In other words, when I make a SELECT statement, the results that come
> back
> > would point to the physical locations of where the raw data exists, or,
> > return the data and stores it in memory, or however the mechanism works.
> > Updating should not affect what rows have been called up.
>
> So you require two copies of the data: one which is the data as it was when
> you started the SELECT command and another will all the updates that have
> taken place since then, until _finalize() is called.  This means that the
> SELECT command must reserve enough memory for the entire results of the
> SELECT command, and copy all the data into it to process the initial SELECT
> command.
>
> So if I had a table which took 5 Gig of disk space and did a "SELECT * FROM
> myTable" my application would suddenly need to reserve 5 Gig of memory to
> store the results.  This would make every SELECT very slow and use a lot of
> memory, useless on platforms which need to respond to button-presses in
> realtime or have limited memory.  So I can understand why SQLite doesn't
> allow it.
>
> You can do this yourself, of course: perform the entire SELECT and store
> the results in one or more variables, then use the contents of those
> variables to decide which UPDATE commands to do.  Or you can just use the
> UPDATE command with a WHERE clause, which does both commands in one go and
> is far more efficient.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Woody & Yuni Ho]

Sql can do it. It just takes a properly designed query.


Woody wizard at large(I'm in shape. Round is a shape)
Connected by MOTOBLUR™ on T-Mobile

-Original message-
From: cricketfan 
To: sqlite-users@sqlite.org
Sent: Mon, Nov 8, 2010 19:24:54 PST
Subject: Re: [sqlite] sqlite3_step to select and update the same table


SQL will not do the trick for me because I based on the select I have to
perform other operations(queries on other tables) and only then can I update
the table in question.

Kees Nuyt wrote:
> 
> There is no need to do this in a loop with a cursor.
> Whenever possible, use the power of SQL set operations.
> As far as I can tell, an alternative solution to
> your problem could be (pseudocode):
> 
> delSql = "UPDATE table1
>SET ghi = ?
>  WHERE def IN (
>   SELECT ref 
> FROM table1
>WHERE abc = ?
>   );";
> prepare(db, delSql, ..., stmt, ...);
> bind_int(stmt, ..., ghivalue);
> bind_text(stmt, ..., abcvalue);
> step(stmt);
> reset(stmt);
> finalize(stmt);
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30166069.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[cricketfan]

SQL will not do the trick for me because I based on the select I have to
perform other operations(queries on other tables) and only then can I update
the table in question.

Kees Nuyt wrote:
> 
> There is no need to do this in a loop with a cursor.
> Whenever possible, use the power of SQL set operations.
> As far as I can tell, an alternative solution to
> your problem could be (pseudocode):
> 
> delSql = "UPDATE table1
>SET ghi = ?
>  WHERE def IN (
>   SELECT ref 
> FROM table1
>WHERE abc = ?
>   );";
> prepare(db, delSql, ..., stmt, ...);
> bind_int(stmt, ..., ghivalue);
> bind_text(stmt, ..., abcvalue);
> step(stmt);
> reset(stmt);
> finalize(stmt);
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30166069.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 8 Nov 2010, at 9:27pm, Stephen Chrzanowski wrote:

> I don't know how the code works, but logically speaking, if I'm at row B,
> and I update row B to E, row B physically remains B but has its data content
> changed to E.  From there on in, it should go on to C then D then F, etc.

And naturally, since you are reading the row and it was retrieved in 'B' order, 
it is vital that the contents of the field is 'B'.  Otherwise your code will 
not find the data in the order it has asked for it.

> Since the full rowset results already exist somewhere, it shouldn't show up
> anywhere else down the line, simply because the updated or even new
> recordset isn't part of the compiled result set list.
> 
> In other words, when I make a SELECT statement, the results that come back
> would point to the physical locations of where the raw data exists, or,
> return the data and stores it in memory, or however the mechanism works.
> Updating should not affect what rows have been called up.

So you require two copies of the data: one which is the data as it was when you 
started the SELECT command and another will all the updates that have taken 
place since then, until _finalize() is called.  This means that the SELECT 
command must reserve enough memory for the entire results of the SELECT 
command, and copy all the data into it to process the initial SELECT command.

So if I had a table which took 5 Gig of disk space and did a "SELECT * FROM 
myTable" my application would suddenly need to reserve 5 Gig of memory to store 
the results.  This would make every SELECT very slow and use a lot of memory, 
useless on platforms which need to respond to button-presses in realtime or 
have limited memory.  So I can understand why SQLite doesn't allow it.

You can do this yourself, of course: perform the entire SELECT and store the 
results in one or more variables, then use the contents of those variables to 
decide which UPDATE commands to do.  Or you can just use the UPDATE command 
with a WHERE clause, which does both commands in one go and is far more 
efficient.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Kees Nuyt]

On Sun, 7 Nov 2010 10:14:29 -0800 (PST), cricketfan
 wrote:

>Just to make things clearer
>the value being fetched into ref from the database, is also the value being
>changed(ghi) in the update statement. When I change my query (just to debug)
>to update some other column in the table the whole thing runs fine and runs
>only once!
>Can someone throw some light on this?

It is still unclear to me why you insist using a cursor.
A single UPDATE statement with a proper WHERE clause should
do the job. I already suggested code for that.

Since your first example wasn't exact, and the second is not
complete I'm not really tempted to look into it.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Stephen Chrzanowski]

I don't know how the code works, but logically speaking, if I'm at row B,
and I update row B to E, row B physically remains B but has its data content
changed to E.  From there on in, it should go on to C then D then F, etc.

Since the full rowset results already exist somewhere, it shouldn't show up
anywhere else down the line, simply because the updated or even new
recordset isn't part of the compiled result set list.

In other words, when I make a SELECT statement, the results that come back
would point to the physical locations of where the raw data exists, or,
return the data and stores it in memory, or however the mechanism works.
Updating should not affect what rows have been called up.

On Sun, Nov 7, 2010 at 9:16 PM, Simon Slavin  wrote:

>
> On 8 Nov 2010, at 2:02am, cricketfan wrote:
>
> > Simon, As per my understanding I am getting the result set and trying to
> > change values in the table based on what I read from the result set up to
> > that point. I see no reason why I should be stopped from updating the row
> I
> > have already read in the result set.
>
> Your update might change whether the row is in the result set.  Or it might
> change /where/ the row falls in the result set.  For instance, suppose you
> have the following rows:
>
> A
> B
> C
> D
> F
>
> You read as far as B, then decide to change the B to an E.  Now what should
> happen ?  Should that record appear again when you get past the D ?  But it
> might be a problem to include the same record twice.  How should SQL know
> what it should do ?  So SQL prevents you from making changes which effect an
> open SELECT.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 8 Nov 2010, at 2:02am, cricketfan wrote:

> Simon, As per my understanding I am getting the result set and trying to
> change values in the table based on what I read from the result set up to
> that point. I see no reason why I should be stopped from updating the row I
> have already read in the result set.

Your update might change whether the row is in the result set.  Or it might 
change /where/ the row falls in the result set.  For instance, suppose you have 
the following rows:

A
B
C
D
F

You read as far as B, then decide to change the B to an E.  Now what should 
happen ?  Should that record appear again when you get past the D ?  But it 
might be a problem to include the same record twice.  How should SQL know what 
it should do ?  So SQL prevents you from making changes which effect an open 
SELECT.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[cricketfan]

Simon, As per my understanding I am getting the result set and trying to
change values in the table based on what I read from the result set up to
that point. I see no reason why I should be stopped from updating the row I
have already read in the result set. 

Simon Slavin-3 wrote:
> 
> 
> On 7 Nov 2010, at 6:14pm, cricketfan wrote:
> I don't understand why you find this surprising.  You have two pieces of
> program: one is trying to look at something while the other is in the
> middle of changing it.  They are going to argue about which one can access
> those values.
> 
> You can complete the scan first, then make the changes you want.  Or if
> the changes depend on the scan, use an UPDATE ... WHERE command that does
> all the work for you.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30157400.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 7 Nov 2010, at 6:14pm, cricketfan wrote:

> Just to make things clearer
> the value being fetched into ref from the database, is also the value being
> changed(ghi) in the update statement. When I change my query (just to debug)
> to update some other column in the table the whole thing runs fine and runs
> only once!
> Can someone throw some light on this?

I don't understand why you find this surprising.  You have two pieces of 
program: one is trying to look at something while the other is in the middle of 
changing it.  They are going to argue about which one can access those values.

You can complete the scan first, then make the changes you want.  Or if the 
changes depend on the scan, use an UPDATE ... WHERE command that does all the 
work for you.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[cricketfan]

Just to make things clearer
the value being fetched into ref from the database, is also the value being
changed(ghi) in the update statement. When I change my query (just to debug)
to update some other column in the table the whole thing runs fine and runs
only once!
Can someone throw some light on this?

cricketfan wrote:
> 
> rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
> while(sqlite3_step(stmt) == SQLITE_ROW) { 
>   ref = sqlite3_column_int(stmt,3);
>   delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
> ;",1,ref);
>   if( (rc = sqlite3_exec(db, delSql, NULL, NULL, )) != SQLITE_OK )
> { sqlite3_free(zErrMsg); goto error; }
>   }
> Thanks
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30153073.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Kees Nuyt]

On Sat, 6 Nov 2010 21:29:58 -0700 (PDT), cricketfan
 wrote:

>I am trying to select some columns from a table and use that to
>update another column in the same table using prepare/step/reset/finalize
>methods. However, when I use the update statement while stepping it is
>executing the flow 2 times. 

There is no need to do this in a loop with a cursor.
Whenever possible, use the power of SQL set operations.
As far as I can tell, an alternative solution to
your problem could be (pseudocode):

delSql = "UPDATE table1
   SET ghi = ?
 WHERE def IN (
SELECT ref 
  FROM table1
 WHERE abc = ?
);";
prepare(db, delSql, ..., stmt, ...);
bind_int(stmt, ..., ghivalue);
bind_text(stmt, ..., abcvalue);
step(stmt);
reset(stmt);
finalize(stmt);

Hope this helps.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 7 Nov 2010, at 4:29am, cricketfan wrote:

>I am trying to select some columns from a table and use that to
> update another column in the same table using prepare/step/reset/finalize
> methods. However, when I use the update statement while stepping it is
> executing the flow 2 times.

You can't UPDATE a table while you're in the middle of stepping through a 
SELECT.  Because the UPDATE might change one of the things that governs the 
results of the SELECT command and cause it to repeat or skip records.  The 
neatest way to do what you want is to first perform the SELECT, read all the 
rows into an array, and finalize it.  Then perform any UPDATE you need.

An alternative would be to address the database as if you're using two 
different processes with two different database handles.  But depending on how 
you're accessing the database this may merely delay your UDPATE thread until 
the SELECT thread is finished.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_step to select and update the same table

[cricketfan]

Hello,
I am trying to select some columns from a table and use that to
update another column in the same table using prepare/step/reset/finalize
methods. However, when I use the update statement while stepping it is
executing the flow 2 times. In order to clarify, I am pasting some pseudo
code

zSql = sqlite3_mprintf("SELECT * FROM table1 WHERE abc=? ;");
if( (rc = sqlite3_prepare_v2(db, zSql, (int)strlen(zSql), , NULL)) !=
SQLITE_OK ) {
fprintf(stderr, "SQL error: preparing update statement\n");
goto error;
}
rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
if( rc != SQLITE_OK ) { goto error;}
/* Execute the query expression */
while(sqlite3_step(stmt) == SQLITE_ROW) { 
ref = sqlite3_column_int(stmt,3);
printf("Before sql execution %d \n",ref);
delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
;",1,ref);
if( (rc = sqlite3_exec(db, delSql, NULL, NULL, )) != SQLITE_OK 
) {
sqlite3_free(zErrMsg); goto error; }
}
sqlite3_reset(stmt);
sqlite3_finalize(stmt);
Output :
Before sql execution 5
Before sql execution 5 
Before sql execution 6 
Before sql execution 6

As you can see the print statement is being printed 2 times and this code is
not in a loop, apart from the while for stepping. If I replace the update
statement with another select statement I behaves normally i.e. just
executes the query once, prints just once.

Do I have to do something special If I want to do an update on the table I
am selecting from? I am new to SQLITE hope someone can help me resolve my
newbie problem.

Thanks
-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30152284.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users