Re: [sqlite] checking if a record exist or not
"Igor Tandetnik" wrote... > jose isaias cabrera > wrote: >> I am updating an sqlite db from another sqlite db and it is working >> fine, unless the record does not exists. ie. >> >> "ATTACH db2 AS client; "; >> >> BEGIN; >> INSERT OR REPLACE INTO LSOpenJobs >> SELECT * FROM client.LSOpenJobs >> WHERE login='x' AND XtraB > '2000'; >> COMMIT; >> >> this works fine if the record already exists in the db getting >> updated. However, if the record was deleted, inadvertively, from the >> db getting updated, this will not work. > > What do you mean, will not work? In precisely what way does the observed > behavior differ from one you expect? I expected the INSERT to insert the record that no longer exists in the main DB. There are two DBs. One that guide the unique record number system and another one, that is a copy of it. I expected, that if a record is deleted from the main DB, but it still existed on another DB, when that user pushed the data to the main DB, the INSERT will find out that it was not there and it would insert it back into the main db. >> Is there a way to insert the record back in the db getting updated if >> it is not there? > > That's precisely what the INSERT part of "INSERT OR REPLACE" would do. I know, but it is not working. > Or are you saying that there's a record that isn't currently in the main > db (perhaps it existed some time ago, but was deleted) nor in the > attached db, and you expect SQLite to somehow magically conjure it up > out of thin air? I don't think real world works quite this way. No. The record exists in the attached DB, but not in the connected db, though, it did exist there. I allowed deletion for records because I expected the INSERT to insert it to the main db. > Igor Tandetnik thanks for the help. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
"P Kishor" wrote > On Thu, Mar 19, 2009 at 12:01 PM, jose isaias cabrera wrote: >> >> Greetings... >> >> I am updating an sqlite db from another sqlite db and it is working fine, >> unless the record does not exists. ie. >> >> "ATTACH db2 AS client; "; >> >> BEGIN; >> INSERT OR REPLACE INTO LSOpenJobs >> SELECT * FROM client.LSOpenJobs >> WHERE login='x' AND XtraB > '2000'; >> COMMIT; >> >> this works fine if the record already exists in the db getting updated. >> However, if the record was deleted, inadvertively, from the db getting >> updated, this will not work. >> >> Is there a way to insert the record back in the db getting updated if it >> is >> not there? You should know that this record did exists previously in this >> db, but it was deleted. > > > José, you didn't communicate the above very well, because the above > sentence makes no sense whatsoever. If you are still in a transaction > and delete something that you didn't mean to, you can always bring it > back by not committing the transaction. Yes, I am kinda new. Heck, I am a newbie and I may not have communicated the correct situation. Never mind, it does work. My fault. I got scared, because I built this system counting on INSERT OR REPLACE to work was the words describe. Thanks guys. False alarm. My apologies. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
"Igor Tandetnik" wrote... > jose isaias cabrera > wrote: >> I am updating an sqlite db from another sqlite db and it is working >> fine, unless the record does not exists. ie. >> >> "ATTACH db2 AS client; "; >> >> BEGIN; >> INSERT OR REPLACE INTO LSOpenJobs >> SELECT * FROM client.LSOpenJobs >> WHERE login='x' AND XtraB > '2000'; >> COMMIT; >> >> this works fine if the record already exists in the db getting >> updated. However, if the record was deleted, inadvertively, from the >> db getting updated, this will not work. > > What do you mean, will not work? In precisely what way does the observed > behavior differ from one you expect? I expected the INSERT to insert the record that no longer exists in the main DB. There are two DBs. One that guide the unique record number system and another one, that is a copy of it. I expected, that if a record is deleted from the main DB, but it still existed on another DB, when that user pushed the data to the main DB, the INSERT will find out that it was not there and it would insert it back into the main db. >> Is there a way to insert the record back in the db getting updated if >> it is not there? > > That's precisely what the INSERT part of "INSERT OR REPLACE" would do. I know, but it is not working. > Or are you saying that there's a record that isn't currently in the main > db (perhaps it existed some time ago, but was deleted) nor in the > attached db, and you expect SQLite to somehow magically conjure it up > out of thin air? I don't think real world works quite this way. No. The record exists in the attached DB, but not in the connected db, though, it did exist there. I allowed deletion for records because I expected the INSERT to insert it to the main db. > Igor Tandetnik thanks for the help. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
On Thu, Mar 19, 2009 at 12:01 PM, jose isaias cabrera wrote: > > Greetings... > > I am updating an sqlite db from another sqlite db and it is working fine, > unless the record does not exists. ie. > > "ATTACH db2 AS client; "; > > BEGIN; > INSERT OR REPLACE INTO LSOpenJobs > SELECT * FROM client.LSOpenJobs > WHERE login='x' AND XtraB > '2000'; > COMMIT; > > this works fine if the record already exists in the db getting updated. > However, if the record was deleted, inadvertively, from the db getting > updated, this will not work. > > Is there a way to insert the record back in the db getting updated if it is > not there? You should know that this record did exists previously in this > db, but it was deleted. José, you didn't communicate the above very well, because the above sentence makes no sense whatsoever. If you are still in a transaction and delete something that you didn't mean to, you can always bring it back by not committing the transaction. If you have already committed the transaction or were not in a transaction to begin with, then you could rely on a backup. Did you say you didn't have a backup? Well, you are out of luck then. How would the database know what to bring back? You could do one other thing -- not delete a record when a user "deletes" it... instead, mark it for deletion, and then never show it to the user. But, that is another story. > > This is the CREATE table schema: > CREATE TABLE LSOpenJobs > ( > id integer primary key, > ProjID integer, > login, > ..., > XtraB, > XtraC, > XtraD, > XtraE, > XtraF > ); > > Thanks for the help, > > josé > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a record exist or not
jose isaias cabrera wrote: > I am updating an sqlite db from another sqlite db and it is working > fine, unless the record does not exists. ie. > > "ATTACH db2 AS client; "; > > BEGIN; > INSERT OR REPLACE INTO LSOpenJobs > SELECT * FROM client.LSOpenJobs > WHERE login='x' AND XtraB > '2000'; > COMMIT; > > this works fine if the record already exists in the db getting > updated. However, if the record was deleted, inadvertively, from the > db getting updated, this will not work. What do you mean, will not work? In precisely what way does the observed behavior differ from one you expect? > Is there a way to insert the record back in the db getting updated if > it is not there? That's precisely what the INSERT part of "INSERT OR REPLACE" would do. Or are you saying that there's a record that isn't currently in the main db (perhaps it existed some time ago, but was deleted) nor in the attached db, and you expect SQLite to somehow magically conjure it up out of thin air? I don't think real world works quite this way. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] checking if a record exist or not
Greetings... I am updating an sqlite db from another sqlite db and it is working fine, unless the record does not exists. ie. "ATTACH db2 AS client; "; BEGIN; INSERT OR REPLACE INTO LSOpenJobs SELECT * FROM client.LSOpenJobs WHERE login='x' AND XtraB > '2000'; COMMIT; this works fine if the record already exists in the db getting updated. However, if the record was deleted, inadvertively, from the db getting updated, this will not work. Is there a way to insert the record back in the db getting updated if it is not there? You should know that this record did exists previously in this db, but it was deleted. This is the CREATE table schema: CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, login, ..., XtraB, XtraC, XtraD, XtraE, XtraF ); Thanks for the help, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
