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), &stmt, 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, &zErrMsg)) != 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

[Black, Michael (IS)]

I think the quick answer is to use a different database handle for your update. I think you may also be able to do this with WAL mode.
http://www.sqlite.org/draft/wal.html
I did some searching and couldn't find a definitive answer for doing an update inside a select loop (though I'm sure I've seen it on this list before).

I was unable to duplicate your problem...perhaps you can modify this to show it

#include <stdio.h>
#include "sqlite3.h"
main()
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *errmsg=NULL;
char *sql;
remove("update.db");
sqlite3_open("update.db",&db);
sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,&errmsg);
sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,&errmsg);
sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,&errmsg);
sql = "SELECT * FROM t where a=1";
sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,NULL);
while(sqlite3_step(stmt)==SQLITE_ROW) {
char sqlbuf[4096];
int ref;
ref = sqlite3_column_int(stmt,1);
printf("Before %d\n",ref);
sprintf(sqlbuf,"UPDATE t set b=b+1 where a=%d",ref);
puts(sqlbuf);

sqlite3_exec(db,sqlbuf,NULL,NULL,&errmsg);
}
sqlite3_finalize(stmt);
sqlite3_close(db);

}

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems


________________________________

From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org on behalf of cricketfan
Sent: Sat 11/6/2010 11:29 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: EXTERNAL:[sqlite] sqlite3_step to select and update the same table




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), &stmt, 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, &zErrMsg)) != 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.

[cricketfan]

Mike I also was not able to reproduce this behavior with a small sample
program and am puzzled by this behavior in my main application. What puzzled
me is
1. Select has criteria a=?, b=?, c=? (prepare statement) - step through it
2. Get the value of d from database based on the above criteria
3. Update the value of d
4. The value of d has now been but the entire module within the step module
is being re-run.

I have been able to get around this by calling sqlite3_clear_bindings and
sqlite3_reset within the step.

Post by Black, Michael (IS)
I think the quick answer is to use a different database handle for your
update. I think you may also be able to do this with WAL mode.
http://www.sqlite.org/draft/wal.html
I did some searching and couldn't find a definitive answer for doing an
update inside a select loop (though I'm sure I've seen it on this list
before).
I was unable to duplicate your problem...perhaps you can modify this to show it
#include <stdio.h>
#include "sqlite3.h"
main()
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *errmsg=NULL;
char *sql;
remove("update.db");
sqlite3_open("update.db",&db);
sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,&errmsg);
sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,&errmsg);
sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,&errmsg);
sql = "SELECT * FROM t where a=1";
sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,NULL);
while(sqlite3_step(stmt)==SQLITE_ROW) {
char sqlbuf[4096];
int ref;
ref = sqlite3_column_int(stmt,1);
printf("Before %d\n",ref);
sprintf(sqlbuf,"UPDATE t set b=b+1 where a=%d",ref);
puts(sqlbuf);
sqlite3_exec(db,sqlbuf,NULL,NULL,&errmsg);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems

[cricketfan]

I thought call to clear bindings and reset was working but I got confused and
am wherever I was before posting on the forum. Still have no conclusive
evidence as to why the database in my application is behaving in a weird
fashion. Any further input would be appreciated.

Post by cricketfan
Mike I also was not able to reproduce this behavior with a small sample
program and am puzzled by this behavior in my main application. What
puzzled me is
1. Select has criteria a=?, b=?, c=? (prepare statement) - step through it
2. Get the value of d from database based on the above criteria
3. Update the value of d
4. The value of d has now been but the entire module within the step
module is being re-run.
I have been able to get around this by calling sqlite3_clear_bindings and
sqlite3_reset within the step.

[Simon Slavin]

Post by cricketfan
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.

[Kees Nuyt]

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

Post by cricketfan
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.

[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.

Post by Kees Nuyt
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
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);

[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 <***@yahoo.co.in>
To: sqlite-***@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.

Post by Kees Nuyt
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
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);

[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?

Post by cricketfan
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, &zErrMsg)) != SQLITE_OK )
{ sqlite3_free(zErrMsg); goto error; }
}
Thanks

[Simon Slavin]

Post by 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?

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.

[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.

Post by Simon Slavin
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by 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.

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.

[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.

Post by Simon Slavin

Post by 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

Post by cricketfan
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
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by 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.

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.

Post by Stephen Chrzanowski
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.

[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.

Post by Simon Slavin

Post by Stephen Chrzanowski
In other words, when I make a SELECT statement, the results that come

back

Post by Stephen Chrzanowski
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Kees Nuyt]

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

Post by 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?

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.