dup records removal 
Author Message
 dup records removal

All,

I'm going through a tutorial in a book I bought and ran a sql
statement twice by mistake. My books is PHP and MySQL Development and
working with the books db.

How do I get rid of the duplicates? Not a big issue but I couldn't
find this in my book.

Appreciate the help,
Ron

Here was th sql statement:
use books;

insert into customers values
  (NULL, "Julie Smith", "25 Oak Street", "Airport West"),
  (NULL, "Alan Wong", "1/47 Haines Avenue", "Box Hill"),
  (NULL, "Michelle Arthur", "357 North Road", "Yarraville");

insert into orders values
  (NULL, 3, 69.98, "02-Apr-2000"),
  (NULL, 1, 49.99, "15-Apr-2000"),
  (NULL, 2, 74.98, "19-Apr-2000"),
  (NULL, 3, 24.99, "01-May-2000");

insert into books values
  ("0-672-31697-8", "Michael Morgan", "Java 2 for Professional
Developers", 34.99),
  ("0-672-31745-1", "Thomas Down", "Installing Debian GNU/Linux",
24.99),
  ("0-672-31509-2", "Pruitt, et al.", "Teach Yourself GIMP in 24
Hours", 24.99),
  ("0-672-31769-9", "Thomas Schenk", "Caldera OpenLinux System
Administration Unleashed", 49.99);

insert into order_items values
  (1, "0-672-31697-8", 2),
  (2, "0-672-31769-9", 1),
  (3, "0-672-31769-9", 1),
  (3, "0-672-31509-2", 1),
  (4, "0-672-31745-1", 3);

insert into book_reviews values
  ("0-672-31697-8", "Morgan's book is clearly written and goes well
beyond most of the basic Java books out there.");



Sat, 30 Jul 2005 11:13:04 GMT  
 dup records removal

Quote:

> All,

> I'm going through a tutorial in a book I bought and ran a sql
> statement twice by mistake. My books is PHP and MySQL Development and
> working with the books db.

> How do I get rid of the duplicates? Not a big issue but I couldn't
> find this in my book.

> Appreciate the help,
> Ron

Well, I would say your best thing would be to add UNIQUE indexes to each
column using ALTER TABLE, and then change it back.  Obviously, you could
also drop the tables and run your scripts again...

Regards,

David



Sat, 30 Jul 2005 17:36:10 GMT  
 dup records removal
On Tue, 11 Feb 2003 09:36:10 +0000, David Jonathan Grant

Quote:


>> All,

>> I'm going through a tutorial in a book I bought and ran a sql
>> statement twice by mistake. My books is PHP and MySQL Development and
>> working with the books db.

>> How do I get rid of the duplicates? Not a big issue but I couldn't
>> find this in my book.

>> Appreciate the help,
>> Ron

>Well, I would say your best thing would be to add UNIQUE indexes to each
>column using ALTER TABLE, and then change it back.  Obviously, you could
>also drop the tables and run your scripts again...

>Regards,

>David

David,

Don't completely understand ther first part. I was thinking there was
a way to find records where all fields matched each other.

On the second part I could definitely delete the table but I know that
someday i'll do this again when I can't delete.

Thx..ron



Sun, 31 Jul 2005 00:43:27 GMT  
 dup records removal

scrawled:

Quote:
>On Tue, 11 Feb 2003 09:36:10 +0000, David Jonathan Grant


>>> All,

>>> I'm going through a tutorial in a book I bought and ran a sql
>>> statement twice by mistake. My books is PHP and MySQL Development and
>>> working with the books db.

>>> How do I get rid of the duplicates? Not a big issue but I couldn't
>>> find this in my book.

>>> Appreciate the help,
>>> Ron

>>Well, I would say your best thing would be to add UNIQUE indexes to each
>>column using ALTER TABLE, and then change it back.  Obviously, you could
>>also drop the tables and run your scripts again...

>>Regards,

>>David

>David,

>Don't completely understand ther first part. I was thinking there was
>a way to find records where all fields matched each other.

>On the second part I could definitely delete the table but I know that
>someday i'll do this again when I can't delete.

RULE 1: ALWAYS HAVE A UNIQUE PRIMARY KEY !
 Even if you never (intendt to) use it - always have a Unique primary key -
  it makes life simpler in these situtions.

 The usual trick is:

   create temporary table T1 select distinct * from TABLE;
   delete from table;
   insert into TABLE select * from T1;



Sun, 31 Jul 2005 06:18:30 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. +R as a name for DUP >R

2. UNDROP an alternative to ?DUP

3. DUP >R

4. dup

5. Memory behavior of String#dup

6. dup, clone, and "deep copy"

7. self being altered even when .dup'ed

8. dup vs clone

9. 5 dup ($)

10. Resb and dup in NASM

11. Problem with DUP

12. Resb and dup in NASM

 

 
Powered by phpBB® Forum Software