Re: [sqlite] one liner for insert or update ?

[Jay A. Kreibich]

On Mon, Sep 14, 2009 at 01:25:29AM -0700, yogibabu scratched on the wall:
> 
> 
> 
> Jean-Christophe Deschamps wrote:
> > 
> > Do you have a short example where "INSERT OR REPLACE INTO ..." can be 
> > shown to actually _delete_ rows?
> > Which query are you refering to?
> 
> sorry, i meant "columns" not rows, whet i do insert command, specifying
> existing id and only some of the columns then the rest of the columns are
> deleted

  The whole row is deleted.  The command is INSERT OR REPLACE, as in
  "INSERT OR (DELETE AND INSERT)", not "INSERT OR UPDATE".  And if you
  want to get real picky, it is actually a variation on "INSERT,
  DELETE ON CONFLICT." In other words, there are situations when an 
  INSERT OR REPLACE can end up DELETEing more than one existing row
  before it INSERTs the new row.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] one liner for insert or update ?

[Jean-Christophe Deschamps]

At 10:25 14/09/2009, you wrote:
´¯¯¯
>sorry, i meant "columns" not rows, whet i do insert command, specifying
>existing id and only some of the columns then the rest of the columns are
>deleted
>on the other hand, UPDATE command won't allow me to insert a new row

Then you can still do something along this:

CREATE TABLE "test" (
   "k" INTEGER PRIMARY KEY ON CONFLICT REPLACE,
   "a" CHAR,
   "b" CHAR);
insert or replace into test (k, a, b) values (234, 'abc', 'uvw');
insert or replace into test (k, a, b) values (123, 'def', 'xyz');

insert or replace into test (k, a, b) values (234, 'won', (select b 
from test where k = 234));

This is a possibility. There might be others.

>there should be a possibility to check id of the last affected row and
>return it to the code
`---

Yes but in a second statement that you have to issue when the key is a 
new one.



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

Re: [sqlite] one liner for insert or update ?

[yogibabu]

Jean-Christophe Deschamps wrote:
> 
> Do you have a short example where "INSERT OR REPLACE INTO ..." can be 
> shown to actually _delete_ rows?
> Which query are you refering to?

sorry, i meant "columns" not rows, whet i do insert command, specifying
existing id and only some of the columns then the rest of the columns are
deleted
on the other hand, UPDATE command won't allow me to insert a new row



>From what I understand, you might be happy with making your Id column 
> Integer Primary Key (also look at autoincrement feature) then do a 
> standard insert or replace.

i have it precisely like that


Also if SQLite is passed a null Id then it will generate the "next" 
> available rowid for you. But then it's no more a one-liner since your 
> application need to know which Id the newly inserted row has been 
> given, typically with a query like Select 

there should be a possibility to check id of the last affected row and
return it to the code
-- 
View this message in context: 
http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25432143.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] one liner for insert or update ?

[Jean-Christophe Deschamps]

At 01:29 14/09/2009, you wrote:
´¯¯¯
>CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
>field1 TEXT
>);
>
>but I think that the table has to already have been created this way.
`---

Yes from what he said, I also believe this is the right and simplest way.



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

Re: [sqlite] one liner for insert or update ?

[Jean-Christophe Deschamps]

I tried twice to post it but it seems it didn't find its way to the 
list. Sorry if ever it gets dupped.


Hi,


>insert or replace deletes all rows that weren't specified in the query

Do you have a short example where "INSERT OR REPLACE INTO ..." can be 
shown to actually _delete_ rows?
Which query are you refering to?


>i don't know maybe in combination with select there's possible to so 
>sth about
>it - like that:
>INSERT OR REPLACE INTO table (id) then SELECT...

INSERT (or update) is a different beast from SELECT.  "Then" is 
unexpected in your statement.


 From what I understand, you might be happy with making your Id column 
Integer Primary Key (also look at autoincrement feature) then do a 
standard insert or replace.  But I can't say if this is suitable for 
your precise situation.

Also if SQLite is passed a null Id then it will generate the "next" 
available rowid for you. But then it's no more a one-liner since your 
application need to know which Id the newly inserted row has been 
given, typically with a query like Select 
sqlite3_last_insert_rowid()
 
.





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

Re: [sqlite] one liner for insert or update ?

[yogibabu]

UIT DEVELOPMENT wrote:
> 
> Are you wanting something like this:
> 
> INSERT INTO 'mytable' (key, 'visitorcount', 'visitdate')
> VALUES (123456789, 1, NOW())
> ON DUPLICATE KEY UPDATE 'visitorcount' = 'visitorcount' + 1
> 
> the key statement being: ON DUPLICATE KEY UPDATE
> 
> not tested but it seems like that is what you are looking for.  Its
> sometimes refered to "upsert" processing...
> 

sqlite error 1 - near "ON": syntax error

-- 
View this message in context: 
http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25431264.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] one liner for insert or update ?

[UIT DEVELOPMENT]

I see this:

CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, field1 TEXT
);

but I think that the table has to already have been created this way.

I found that in the docs: http://sqlite.org/lang_conflict.html

Would that help the OP?


On Sun, Sep 13, 2009 at 6:56 PM, Jean-Christophe Deschamps
 wrote:
>
>
> ´¯¯¯
>>INSERT INTO 'mytable' (key, 'visitorcount', 'visitdate')
>>VALUES (123456789, 1, NOW())
>>ON DUPLICATE KEY UPDATE 'visitorcount' = 'visitorcount' + 1
>>
>>the key statement being: ON DUPLICATE KEY UPDATE
>>
>>not tested but it seems like that is what you are looking for.  Its
>>sometimes refered to "upsert" processing...
> `---
>
> Is this _really_ a SQLite syntax?
>
>
>
> ___
> 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] one liner for insert or update ?

[UIT DEVELOPMENT]

Geez.  I am sorry.  You are correct.  This is MySQL.   Sorry about
that and to the OP.   (banging forehead with open faced palm of my
hand!!!)

Tony

On Sun, Sep 13, 2009 at 6:56 PM, Jean-Christophe Deschamps
 wrote:
>
>
> ´¯¯¯
>>INSERT INTO 'mytable' (key, 'visitorcount', 'visitdate')
>>VALUES (123456789, 1, NOW())
>>ON DUPLICATE KEY UPDATE 'visitorcount' = 'visitorcount' + 1
>>
>>the key statement being: ON DUPLICATE KEY UPDATE
>>
>>not tested but it seems like that is what you are looking for.  Its
>>sometimes refered to "upsert" processing...
> `---
>
> Is this _really_ a SQLite syntax?
>
>
>
> ___
> 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] one liner for insert or update ?

[Jean-Christophe Deschamps]

´¯¯¯
>INSERT INTO 'mytable' (key, 'visitorcount', 'visitdate')
>VALUES (123456789, 1, NOW())
>ON DUPLICATE KEY UPDATE 'visitorcount' = 'visitorcount' + 1
>
>the key statement being: ON DUPLICATE KEY UPDATE
>
>not tested but it seems like that is what you are looking for.  Its
>sometimes refered to "upsert" processing...
`---

Is this _really_ a SQLite syntax?



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

Re: [sqlite] one liner for insert or update ?

[UIT DEVELOPMENT]

Are you wanting something like this:

INSERT INTO 'mytable' (key, 'visitorcount', 'visitdate')
VALUES (123456789, 1, NOW())
ON DUPLICATE KEY UPDATE 'visitorcount' = 'visitorcount' + 1

the key statement being: ON DUPLICATE KEY UPDATE

not tested but it seems like that is what you are looking for.  Its
sometimes refered to "upsert" processing...

Tony


On Sat, Sep 12, 2009 at 12:50 PM, yogibabu  wrote:
>
> my dream is to be able do it like that:
>  $pdo = new PDO('sqlite:database.DB3');
> $pdo->query("INSERT UPDATE table SET value1='somedata', value2=somedata,
> id=$getid ")
> ?>
>
> when $getid contains data (a number of course) then an update is performed,
> otherwise if it is null then database engine ignores `id` valule without any
> notification and INSERT is performed with adding new id...
> --
> View this message in context: 
> http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25416164.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] one liner for insert or update ?

[yogibabu]

Doug-4 wrote:
> 
> Wouldn't "INSERT OR REPLACE" do that for you? (which by the way, has to be
> one of the coolest features in SQLite of all!)
> 
> http://www.sqlite.org/lang_insert.html
> 

insert or replace deletes all rows that weren't specified in the query, i
don't know maybe in combination with select there's possible to so sth about
it - like that:
INSERT OR REPLACE INTO table (id) then SELECT...
-- 
View this message in context: 
http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25424616.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] one liner for insert or update ?

[Doug]

Wouldn't "INSERT OR REPLACE" do that for you? (which by the way, has to be
one of the coolest features in SQLite of all!)

http://www.sqlite.org/lang_insert.html


Doug

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of yogibabu
> Sent: Saturday, September 12, 2009 11:51 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] one liner for insert or update ?
> 
> 
> my dream is to be able do it like that:
>  $pdo = new PDO('sqlite:database.DB3');
> $pdo->query("INSERT UPDATE table SET value1='somedata',
> value2=somedata,
> id=$getid ")
> ?>
> 
> when $getid contains data (a number of course) then an update is
> performed,
> otherwise if it is null then database engine ignores `id` valule
> without any
> notification and INSERT is performed with adding new id...
> --
> View this message in context: http://www.nabble.com/one-liner-for-
> insert-or-update---tp25416164p25416164.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

[sqlite] one liner for insert or update ?

[yogibabu]

my dream is to be able do it like that:
query("INSERT UPDATE table SET value1='somedata', value2=somedata,
id=$getid ")
?>

when $getid contains data (a number of course) then an update is performed,
otherwise if it is null then database engine ignores `id` valule without any
notification and INSERT is performed with adding new id...
-- 
View this message in context: 
http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25416164.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