Re: [sqlite] deleting dupicate rows

[Simon Slavin]

On 17 Apr 2014, at 1:21am, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> If you really want to do it in the TABLE definition, use the SQLite
>> shell tool to '.dump' the table as a set of SQL commands, edit the
>> dump file to add that constraint, then use the SQLite tool to '.read'
>> the SQL command file.
> 
> Why not just create the new table, then
> 
>   insert into S select * from R;
>   drop table R;
>   alter table S rename to R;
> 
> or similar?

You're right.  I can only come up with two reasons:

(A) The one that made me make the mistake: I'm not yet used to the INSERT ... 
SELECT facility SQLite provides.
(B) The other one: FOREIGN KEYS can prevent you from deleting the first TABLE.

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

Re: [sqlite] deleting dupicate rows

[James K. Lowden]

On Wed, 16 Apr 2014 16:27:01 +0100
Simon Slavin  wrote:

> If you really want to do it in the TABLE definition, use the SQLite
> shell tool to '.dump' the table as a set of SQL commands, edit the
> dump file to add that constraint, then use the SQLite tool to '.read'
> the SQL command file.

Why not just create the new table, then

insert into S select * from R;
drop table R;
alter table S rename to R;

or similar?  

If you're going to write out the data to a text file, wouldn't it be
faster to export the data to a delimited file and reload them
with .import? Why wrap them up in SQL?  

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

Re: [sqlite] deleting dupicate rows

[Simon Slavin]

On 16 Apr 2014, at 4:02pm, Christoph P.U. Kukulies  wrote:

> Am 16.04.2014 15:57, schrieb Richard Hipp:
>> 
>> CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date);
> 
> Though this seems to work, could I achieve this also by a table constraint, 
> like UNIQUE(major,minor,date) ?

Yes.  In the CREATE TABLE command, after your list of columns but inside the 
same set of brackets, add

, CONSTRAINT con_1 UNIQUE (major,minor,date) ON CONFLICT IGNORE

> Can I apply that a posteriori to the table? I tried SQLite database browser 
> 2.0.b1 but can't figure out how.

No.  There's no way to add it once you have defined the table.  Which is 
probably why Dr Hipp suggested doing it as an INDEX.

If you really want to do it in the TABLE definition, use the SQLite shell tool 
to '.dump' the table as a set of SQL commands, edit the dump file to add that 
constraint, then use the SQLite tool to '.read' the SQL command file.

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

Re: [sqlite] deleting dupicate rows

[Christoph P.U. Kukulies]

Am 16.04.2014 15:57, schrieb Richard Hipp:

On Wed, Apr 16, 2014 at 9:46 AM, Christoph P.U. Kukulies
wrote:


Maybe been asked a hundred times but Im seeking for an elegant way to get
rid of duplicate rows which had been entered during development.
I have a database "versionen.sq3" having a table created by

CREATE TABLE version (major TEXT, minor TEST, date DATE)

Due to running across an iPython notebook several times it happened that I
now have every row occuring as duplicate around five times or so.

Two questions:

1.  would like to delete all duplicate rows by some SQL statement


DELETE FROM version WHERE rowid NOT IN
   (SELECT min(rowid) FROM version GROUP BY major, minor, date);



OK, fine. Worked perfectly.


2. defend myself against this happening again, that is, major,minor and
date may only occur one time in ther respective combination.


CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date);


Though this seems to work, could I achieve this also by a table 
constraint, like UNIQUE(major,minor,date) ?
Can I apply that a posteriori to the table? I tried SQLite database 
browser 2.0.b1 but can't figure out how.



I think I'll have to create some UNIQUE Key. Also, when INSERTing I would
like to avoid error messages being thrown in case of a duplicate row coming
along.



--
Christoph

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

Re: [sqlite] deleting dupicate rows

[Richard Hipp]

On Wed, Apr 16, 2014 at 9:46 AM, Christoph P.U. Kukulies
wrote:

> Maybe been asked a hundred times but Im seeking for an elegant way to get
> rid of duplicate rows which had been entered during development.
> I have a database "versionen.sq3" having a table created by
>
> CREATE TABLE version (major TEXT, minor TEST, date DATE)
>
> Due to running across an iPython notebook several times it happened that I
> now have every row occuring as duplicate around five times or so.
>
> Two questions:
>
>1.  would like to delete all duplicate rows by some SQL statement
>

DELETE FROM version WHERE rowid NOT IN
  (SELECT min(rowid) FROM version GROUP BY major, minor, date);



>
>2. defend myself against this happening again, that is, major,minor and
> date may only occur one time in ther respective combination.
>

CREATE UNIQUE INDEX version_idx1 ON version(major,minor,date);



>
> I think I'll have to create some UNIQUE Key. Also, when INSERTing I would
> like to avoid error messages being thrown in case of a duplicate row coming
> along.
>
> Thanks.
>
> --
> Christoph
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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