Delete Duplicate Rows

Anon

Hi, I have a table with four columns and many rows.

I need to delete the duplicate rows, where the first three column entries are the same, and the fourth column entry is different.

This query shows me what the duplicates are:

select field1,field2,field3,count(*) as cnt from table1 group by field1,field2,field3 having cnt >1;

How can I delete the duplicate rows and keep the original rows?

Thanks a lot.

Anon

Not a particularly elegant solution, but you could setup your intial select query as you mentioned and simply skip through the query comparing each row to the previous row.. for rows that match, delete the current row (delete query based on the primary key) .. if the rows do not match, then update your compare variables...

some psuedo kode:

$field1 = '';
while ($row = mysql_fetch_array($result)) {
if ($field1 == $row['field1']) {
$sql = "delete query here ... ";
mysql_query($sql);
} else {
$field1 = $row['field1']; // not a match, update the compare vars
} // end if
} // end while

john

Anon

I never set up a primary key for the database (I don't know what that is).

The query I mentioned before lists all the rows that are duplicates in the first three of four columns, so I know exactly which rows must be deleted (delete the second duplicate row, I want to keep the first row).

For example I have:

| a | b | c | d |

| a | b | c | x |

I want to delete the second row, because the second row is a duplicate. It does not matter if the fourth field is different.

I could use the query:

insert into newtable select distinct field1,field2,field3,field4 from table;

But in the duplicate rows, since field4 is different, it would copy both rows, when I only need the first row, since it does not matter if field4 is different.

I could use the query:

insert into newtable select distinct field1,field2,field3 from table;

But then it would not copy field4.

So I want to get rid of the second rows where field1, field2, and field3 is the same, and field4 is different.

Is there a way to copy the rows to a new table where only fields 1, 2 and 3 are distinct, and the fourth field can be different?

Thanks so much for any help.

[deleted]

A primary-key is a key that forces uniqueness.

That means that you can force the first three columns to always have a unique combination of data.

I suggest you read the manual of your database for more information about keys and indices.

A simple way of removing duplicates is to create a new table, put a primary key or unique-constraint on the first three columns (the ones you want to be unique)
and simply insert all the rows from the original table into the new table. The unique-constraint will then refuse to insert all the rows that are have duplicates in the first three columns.
Then drop the old table, rename the new table to the name of the old table, (or empty the old table and copy the data from the new to the old table) and you're done.

Anon

hello,
there is an simple query to do that, but what u need is u should have an identity column in that table. If ur not having, alter ur table to add identity coulmn. U can drop this after ur work done.

create table dup(col1 int,id int identity)

delete from dup where id<(select max(id) from dup d where d.col1=dup.col1)

I hope this help a greate for u,

bye,
venkat.
venkat_26178@yahoo.com

[deleted]

Only one tiny problem with that query:
MySQL does not support sub-queries, and 90% of the people here use MySQL.

Anon

Part of the problem was I could not add primary keys to the first three fields, because the first three fields were type tinytext (the fourth field was type text). I copied the table structure to a test table, and changed the first three fields to type varchar(100), but then I could not add a primary key to all of the first three fields, because they added up to 300, which is over 255, so I made them varchar(50), which was enough space for what I had, and added primary keys to the first three fields. Then like vincent said, I just copied the data to the test table, and it added all the rows except for the duplicates. I even checked, using the count query in my first post, and it worked perfectly. Thanks a lot for all your help!

[deleted]

"Part of the problem was I could not add primary keys to the first three fields, because the first three fields were type tinytext"

Wy would that stop you from adding a primary key to them?

..and why on earth did you change the columns to varchar()? why not the INTEGER type?

Anon

I don't think you can make fields with type tinytext primary, index, or unique. I tried and it said:

Error

SQL-query:

CREATE TABLE test (field1 TINYTEXT not null , field2 TINYTEXT not null , field3 TINYTEXT not null , field4 TEXT not null , PRIMARY KEY (field1, field2, field3))

MySQL said: BLOB column 'field1' can't be used in key specification with the used table type

I changed the columns to type varchar(50), because I think the integer type is only for numbers, and they needed to be for words that were no longer than 50 characters. Plus I think that type tinytext is for multi-line entries, whereas I only needed on line for the entry of my words.

Plus my database is still called "test", so you can tell I don't really know what I'm doing. I am learning by trial and error, and of course with the help of other people like you. But it seems to work better now, because it doesn't lock up or crash, even though it is still as slow as a snail!

[deleted]

You'd do well to read the mysql manual about what each datatype can contain.
Anything up to 255 characters of text should be char(if the length is constant) or varchar (if the length varies).

Types like TINYTEXT and TEXT are the same as varchar except that they can hold more than 255 characters.

all character type can hold linebreaks.

Which operations are slow?

Anon

Displaying the php display pages were slow, and also the count pages. Everyday I used to get about 10 "the cpu is heavily loaded" emails with the load averages up to 35 at the most!

I changed the columns from tinytext to varchar(50) because they were usually only about 10-20 characters long and on a single line, and I stopped getting all the email error messages.

Now I just need to upgrade the dedicated server from 128mb to 256mb and everything should be much better!

Thanks for all your help.

[deleted]

"I changed the columns from tinytext to varchar(50) because they were usually only about 10-20 characters long and on a single line"

there is no 'multiline' and 'singleline' there are only strings with and without linebreaks.

varchar() is always slower than char().
If the maximum size of your string is 20 chars, you are better off changing the column type to char(20) or char(30).
That will use a few extra bytes in the database, but it also means less work for the database. (note: only usefull if there are no TEXT or TINYTEXT or VARCHAR columns left in the table)