checking if a record exist or not

classic Classic list List threaded Threaded
6 messages Options
jic
Reply | Threaded
Open this post in threaded view
|

checking if a record exist or not

jic

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a record exist or not

Igor Tandetnik
jose isaias cabrera <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a record exist or not

Puneet Kishor-2
In reply to this post by jic
On Thu, Mar 19, 2009 at 12:01 PM, jose isaias cabrera
<[hidden email]> 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
> [hidden email]
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: checking if a record exist or not

jose isaias cabrera

"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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: checking if a record exist or not

jic
In reply to this post by Puneet Kishor-2
"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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: checking if a record exist or not

jic
In reply to this post by Puneet Kishor-2
"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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users