Re: [sqlite] Primary Key uniqueness
On 03/29/2012 06:27 PM, Joe Bennett wrote: > Hi, > > I have three sqlite3 datbases that currently have no primary key (actually > done with two and stuck on the third). I am converting them to 'new' tables > with a primary key like so: > > create table if not exists new_table > (Column_1,Column_2,Column_3..,Column_47, primary key(Column_1, > Column_27, Column_47)) > > Now, when I insert the data from old_table into new_table, I get the error > message 'columns Column_1,Column_27,Column_47 are not unique' using this > syntax: > > insert into new_table (Column_1,Column_2,Column_3..,Column_47) select * > from old_table You can use: insert or ignore into new_table... insert or replace into new_table... and later find a match with "select" which data didn't get into the new table, if they are important. /Patrik > > That's OK, shame on me for not checking that prior to the insert... Now, I > am using this syntax to find the dupes and remove them: > > select rowid, Column_1,Column_27,Column_47,count(*) > from old_table > group by Column_1,Column_27,Column_47 > having count(*) > 1 > > > No rows meet this criteria... So, I'm looking for a better way to find the > non unique data in the old_table so I can clear this error and insert it > into the new table... Any ideas? > > > > > -Joe > ___ > 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] Primary Key uniqueness
Try omitting 'rowid' - you are not grouping by it and if you did it is unique so no dups. select Column_1,Column_27,Column_47 , count(*) from old_table group by Column_1,Column_27,Column_47 having count(*) > 1 JG On 29 March 2012 17:27, Joe Bennett wrote: > Hi, > > I have three sqlite3 datbases that currently have no primary key (actually > done with two and stuck on the third). I am converting them to 'new' tables > with a primary key like so: > > create table if not exists new_table > (Column_1,Column_2,Column_3..,Column_47, primary key(Column_1, > Column_27, Column_47)) > > Now, when I insert the data from old_table into new_table, I get the error > message 'columns Column_1,Column_27,Column_47 are not unique' using this > syntax: > > insert into new_table (Column_1,Column_2,Column_3..,Column_47) select * > from old_table > > That's OK, shame on me for not checking that prior to the insert... Now, I > am using this syntax to find the dupes and remove them: > > select rowid, Column_1,Column_27,Column_47,count(*) > from old_table > group by Column_1,Column_27,Column_47 > having count(*) > 1 > > > No rows meet this criteria... So, I'm looking for a better way to find the > non unique data in the old_table so I can clear this error and insert it > into the new table... Any ideas? > > > > > -Joe > ___ > 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] Primary Key uniqueness
On 29 Mar 2012, at 5:27pm, Joe Bennett wrote: > No rows meet this criteria... So, I'm looking for a better way to find the > non unique data in the old_table so I can clear this error and insert it > into the new table... Any ideas? Instead of using the plain INSERT ... command you can use the variants INSERT OR IGNORE ... INSERT OR REPLACE ... Which one you use depends on the order you're getting your old data in, and which record you actually want. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Primary Key uniqueness
Hi, I have three sqlite3 datbases that currently have no primary key (actually done with two and stuck on the third). I am converting them to 'new' tables with a primary key like so: create table if not exists new_table (Column_1,Column_2,Column_3..,Column_47, primary key(Column_1, Column_27, Column_47)) Now, when I insert the data from old_table into new_table, I get the error message 'columns Column_1,Column_27,Column_47 are not unique' using this syntax: insert into new_table (Column_1,Column_2,Column_3..,Column_47) select * from old_table That's OK, shame on me for not checking that prior to the insert... Now, I am using this syntax to find the dupes and remove them: select rowid, Column_1,Column_27,Column_47,count(*) from old_table group by Column_1,Column_27,Column_47 having count(*) > 1 No rows meet this criteria... So, I'm looking for a better way to find the non unique data in the old_table so I can clear this error and insert it into the new table... Any ideas? -Joe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
