Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

[Tim Romano]

If you want to "combine two tables" by copying rows from TableB into 
TableA, and TableA has an autoincrementing primary key, there is no need 
to reference the PK column in the SQL statement:

insert into TableA(address, weight)
select address, weight from TableB

The approach above does indeed "bypass the primary key conflict" because 
it leaves it up to TableA to assign the PK value upon insert.  Now, I 
don't know what inflexible "program" you are using that doesn't give you 
control over which columns you want to select. Maybe your "program" will 
let you create a view on TableB?

create view MyView as select address, weight from TableB

and then you could

insert into TableA(address, weight)
select address, weight from MyView

Regards
Tim Romano


On 3/15/2010 9:32 AM, dravid11 wrote:
> Well the situation is that i am merging data of one table in data of another
> table using a program
> so it is going to add all values by it self .I am not actually using insert
> query in that case to select values to add.
>
>
> There is another scenario , what if inserting a data and i want to bypass
> the primary key conflict and just update rest of the values.
>
> again i am combing two tables together so it should take all columns .
>
>
>
> Tim Romano wrote:
>
>> If all you want to do is to insert a new row, do not mention the primary
>> key column name in the insert statement:
>>
>> INSERT INTO temp (address, weight)
>> values( "blah blah", 100)
>>
>> The autoincrementing primary key will be autoincremented.
>>
>> Regards
>> Tim Romano
>>
>>
>> On 3/15/2010 9:15 AM, dravid11 wrote:
>>  
>>> Hello !
>>> I have been trying to search for this solutions for days,yet did not find
>>> the solution.
>>>
>>>
>>> I want to write an insert query on a table. When there is a conflicting
>>> primary key
>>> then it should increment the primary key and insert the row .
>>>
>>> For example i want to run this query
>>> INSERT INTO temp VALUES("1","112","112");
>>>
>>> Here first column is auto increment primary Key.
>>> This query will add the record(20,112,112) in the table
>>> Now when i run the query as
>>>
>>> INSERT INTO temp VALUES("1","100","100");
>>>
>>> I want the result as (2,100,100)
>>>
>>> But it give unique constraint error.
>>> How can i do that ,i.e, duplicate primary key comes in a table insert the
>>> new record and change the primary key of the new record.
>>>
>>> I have used this query but did not work
>>>
>>> insert or replace INTO temp (tempID,Address,Weight)
>>> VALUES(new."tempID",new."Address",new."Weight") ;
>>> SELECT RAISE(IGNORE);
>>> END
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>  
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10 
> 03:33:00
>
>

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

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

[P Kishor]

On Mon, Mar 15, 2010 at 9:18 AM, dravid11  wrote:
>
> As i told you before i am using this database inside an application which is
> merging/combining one source database table to destination database table.
>
> So in that case i am not writing the actual insert query.and in that case
> all columns are taken.
>
> The trigger inside the database should handle that when there is a duplicate
> primary key then it should auto increment this primary key and insert the
> new record with it .
>
>


I am not sure what help you want from the list. I mean, if you can't
change the application that is accessing the db, then use a different
application. If you can't change the INSERT query, how can you change
the "trigger inside the database."

I also suggest you read the sqlite docs on INTEGER PRIMARY KEY.


>
>
>
> P Kishor-3 wrote:
>>
>> On Mon, Mar 15, 2010 at 8:32 AM, dravid11  wrote:
>>>
>>> Well the situation is that i am merging data of one table in data of
>>> another
>>> table using a program
>>> so it is going to add all values by it self .I am not actually using
>>> insert
>>> query in that case to select values to add.
>>>
>>>
>>> There is another scenario , what if inserting a data and i want to bypass
>>> the primary key conflict and just update rest of the values.
>>>
>>
>> INSERT INTO t1 (not_a_pk1, not_a_pk2...)
>> SELECT not_a_pk1, not_a_pk2... FROM t2;
>>
>>
>> And, please don't use double quotes to quote your strings. Use single
>> quotes instead.
>>
>>
>>> again i am combing two tables together so it should take all columns .
>>>
>>>
>>>
>>> Tim Romano wrote:

 If all you want to do is to insert a new row, do not mention the primary
 key column name in the insert statement:

 INSERT INTO temp (address, weight)
 values( "blah blah", 100)

 The autoincrementing primary key will be autoincremented.

 Regards
 Tim Romano


 On 3/15/2010 9:15 AM, dravid11 wrote:
> Hello !
> I have been trying to search for this solutions for days,yet did not
> find
> the solution.
>
>
> I want to write an insert query on a table. When there is a conflicting
> primary key
> then it should increment the primary key and insert the row .
>
> For example i want to run this query
> INSERT INTO temp VALUES("1","112","112");
>
> Here first column is auto increment primary Key.
> This query will add the record(20,112,112) in the table
> Now when i run the query as
>
> INSERT INTO temp VALUES("1","100","100");
>
> I want the result as (2,100,100)
>
> But it give unique constraint error.
> How can i do that ,i.e, duplicate primary key comes in a table insert
> the
> new record and change the primary key of the new record.
>
> I have used this query but did not work
>
> insert or replace INTO temp (tempID,Address,Weight)
> VALUES(new."tempID",new."Address",new."Weight") ;
> SELECT RAISE(IGNORE);
> END
>

 ___
 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/On-conflicting-Primary-key-how-to-insert-record-and-increment-the-Key-tp27904087p27904288.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
>>>
>>
>>
>>
>> --
>> Puneet Kishor
>> ___
>> 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] On conflicting Primary key how to insert record and increment the Key

[dravid11]

As i told you before i am using this database inside an application which is
merging/combining one source database table to destination database table. 

So in that case i am not writing the actual insert query.and in that case
all columns are taken.

The trigger inside the database should handle that when there is a duplicate
primary key then it should auto increment this primary key and insert the
new record with it .





P Kishor-3 wrote:
> 
> On Mon, Mar 15, 2010 at 8:32 AM, dravid11  wrote:
>>
>> Well the situation is that i am merging data of one table in data of
>> another
>> table using a program
>> so it is going to add all values by it self .I am not actually using
>> insert
>> query in that case to select values to add.
>>
>>
>> There is another scenario , what if inserting a data and i want to bypass
>> the primary key conflict and just update rest of the values.
>>
> 
> INSERT INTO t1 (not_a_pk1, not_a_pk2...)
> SELECT not_a_pk1, not_a_pk2... FROM t2;
> 
> 
> And, please don't use double quotes to quote your strings. Use single
> quotes instead.
> 
> 
>> again i am combing two tables together so it should take all columns .
>>
>>
>>
>> Tim Romano wrote:
>>>
>>> If all you want to do is to insert a new row, do not mention the primary
>>> key column name in the insert statement:
>>>
>>> INSERT INTO temp (address, weight)
>>> values( "blah blah", 100)
>>>
>>> The autoincrementing primary key will be autoincremented.
>>>
>>> Regards
>>> Tim Romano
>>>
>>>
>>> On 3/15/2010 9:15 AM, dravid11 wrote:
 Hello !
 I have been trying to search for this solutions for days,yet did not
 find
 the solution.


 I want to write an insert query on a table. When there is a conflicting
 primary key
 then it should increment the primary key and insert the row .

 For example i want to run this query
 INSERT INTO temp VALUES("1","112","112");

 Here first column is auto increment primary Key.
 This query will add the record(20,112,112) in the table
 Now when i run the query as

 INSERT INTO temp VALUES("1","100","100");

 I want the result as (2,100,100)

 But it give unique constraint error.
 How can i do that ,i.e, duplicate primary key comes in a table insert
 the
 new record and change the primary key of the new record.

 I have used this query but did not work

 insert or replace INTO temp (tempID,Address,Weight)
 VALUES(new."tempID",new."Address",new."Weight") ;
 SELECT RAISE(IGNORE);
 END

>>>
>>> ___
>>> 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/On-conflicting-Primary-key-how-to-insert-record-and-increment-the-Key-tp27904087p27904288.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
>>
> 
> 
> 
> -- 
> Puneet Kishor
> ___
> 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/On-conflicting-Primary-key-how-to-insert-record-and-increment-the-Key-tp27904087p27904948.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] On conflicting Primary key how to insert record and increment the Key

[P Kishor]

On Mon, Mar 15, 2010 at 8:32 AM, dravid11  wrote:
>
> Well the situation is that i am merging data of one table in data of another
> table using a program
> so it is going to add all values by it self .I am not actually using insert
> query in that case to select values to add.
>
>
> There is another scenario , what if inserting a data and i want to bypass
> the primary key conflict and just update rest of the values.
>

INSERT INTO t1 (not_a_pk1, not_a_pk2...)
SELECT not_a_pk1, not_a_pk2... FROM t2;


And, please don't use double quotes to quote your strings. Use single
quotes instead.


> again i am combing two tables together so it should take all columns .
>
>
>
> Tim Romano wrote:
>>
>> If all you want to do is to insert a new row, do not mention the primary
>> key column name in the insert statement:
>>
>> INSERT INTO temp (address, weight)
>> values( "blah blah", 100)
>>
>> The autoincrementing primary key will be autoincremented.
>>
>> Regards
>> Tim Romano
>>
>>
>> On 3/15/2010 9:15 AM, dravid11 wrote:
>>> Hello !
>>> I have been trying to search for this solutions for days,yet did not find
>>> the solution.
>>>
>>>
>>> I want to write an insert query on a table. When there is a conflicting
>>> primary key
>>> then it should increment the primary key and insert the row .
>>>
>>> For example i want to run this query
>>> INSERT INTO temp VALUES("1","112","112");
>>>
>>> Here first column is auto increment primary Key.
>>> This query will add the record(20,112,112) in the table
>>> Now when i run the query as
>>>
>>> INSERT INTO temp VALUES("1","100","100");
>>>
>>> I want the result as (2,100,100)
>>>
>>> But it give unique constraint error.
>>> How can i do that ,i.e, duplicate primary key comes in a table insert the
>>> new record and change the primary key of the new record.
>>>
>>> I have used this query but did not work
>>>
>>> insert or replace INTO temp (tempID,Address,Weight)
>>> VALUES(new."tempID",new."Address",new."Weight") ;
>>> SELECT RAISE(IGNORE);
>>> END
>>>
>>
>> ___
>> 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/On-conflicting-Primary-key-how-to-insert-record-and-increment-the-Key-tp27904087p27904288.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
>



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

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

[dravid11]

Well the situation is that i am merging data of one table in data of another
table using a program 
so it is going to add all values by it self .I am not actually using insert
query in that case to select values to add.


There is another scenario , what if inserting a data and i want to bypass
the primary key conflict and just update rest of the values.

again i am combing two tables together so it should take all columns .



Tim Romano wrote:
> 
> If all you want to do is to insert a new row, do not mention the primary 
> key column name in the insert statement:
> 
> INSERT INTO temp (address, weight)
> values( "blah blah", 100)
> 
> The autoincrementing primary key will be autoincremented.
> 
> Regards
> Tim Romano
> 
> 
> On 3/15/2010 9:15 AM, dravid11 wrote:
>> Hello !
>> I have been trying to search for this solutions for days,yet did not find
>> the solution.
>>
>>
>> I want to write an insert query on a table. When there is a conflicting
>> primary key
>> then it should increment the primary key and insert the row .
>>
>> For example i want to run this query
>> INSERT INTO temp VALUES("1","112","112");
>>
>> Here first column is auto increment primary Key.
>> This query will add the record(20,112,112) in the table
>> Now when i run the query as
>>
>> INSERT INTO temp VALUES("1","100","100");
>>
>> I want the result as (2,100,100)
>>
>> But it give unique constraint error.
>> How can i do that ,i.e, duplicate primary key comes in a table insert the
>> new record and change the primary key of the new record.
>>
>> I have used this query but did not work
>>
>> insert or replace INTO temp (tempID,Address,Weight)
>> VALUES(new."tempID",new."Address",new."Weight") ;
>> SELECT RAISE(IGNORE);
>> END
>>
> 
> ___
> 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/On-conflicting-Primary-key-how-to-insert-record-and-increment-the-Key-tp27904087p27904288.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] On conflicting Primary key how to insert record and increment the Key

[Tim Romano]

If all you want to do is to insert a new row, do not mention the primary 
key column name in the insert statement:

INSERT INTO temp (address, weight)
values( "blah blah", 100)

The autoincrementing primary key will be autoincremented.

Regards
Tim Romano


On 3/15/2010 9:15 AM, dravid11 wrote:
> Hello !
> I have been trying to search for this solutions for days,yet did not find
> the solution.
>
>
> I want to write an insert query on a table. When there is a conflicting
> primary key
> then it should increment the primary key and insert the row .
>
> For example i want to run this query
> INSERT INTO temp VALUES("1","112","112");
>
> Here first column is auto increment primary Key.
> This query will add the record(20,112,112) in the table
> Now when i run the query as
>
> INSERT INTO temp VALUES("1","100","100");
>
> I want the result as (2,100,100)
>
> But it give unique constraint error.
> How can i do that ,i.e, duplicate primary key comes in a table insert the
> new record and change the primary key of the new record.
>
> I have used this query but did not work
>
> insert or replace INTO temp (tempID,Address,Weight)
> VALUES(new."tempID",new."Address",new."Weight") ;
> SELECT RAISE(IGNORE);
> END
>

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

[sqlite] On conflicting Primary key how to insert record and increment the Key

[dravid11]

Hello ! 
I have been trying to search for this solutions for days,yet did not find
the solution.


I want to write an insert query on a table. When there is a conflicting
primary key 
then it should increment the primary key and insert the row .

For example i want to run this query 
INSERT INTO temp VALUES("1","112","112");

Here first column is auto increment primary Key. 
This query will add the record(20,112,112) in the table
Now when i run the query as 

INSERT INTO temp VALUES("1","100","100");

I want the result as (2,100,100)

But it give unique constraint error. 
How can i do that ,i.e, duplicate primary key comes in a table insert the
new record and change the primary key of the new record.

I have used this query but did not work

insert or replace INTO temp (tempID,Address,Weight)
VALUES(new."tempID",new."Address",new."Weight") ;
SELECT RAISE(IGNORE);
END


-- 
View this message in context: 
http://old.nabble.com/On-conflicting-Primary-key-how-to-insert-record-and-increment-the-Key-tp27904087p27904087.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