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 <jammer10...@ka3nam.com> 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
