Prev: functions and global variables
Next: help using phpadmin
From: Richard Quadling on 19 Jul 2010 10:01
On 19 July 2010 05:44, Peter <peters(a)egrabber.com> wrote:
> Hi All,
>
> I have a  table which contain's some duplicate rows. I just want to delete
> the duplicate records alone
> not original records.
>
> Assume my table as look as below
>
> column1 column2
> 1
>        a
> 1
>        a
> 2
>        b
> 3
>        c
> 3
>        c
>
>
>
> i want the above table need  to be as below, After executing the mysql
> query.
>
> column1
>        column2
> 1
>        a
> 2
>        b
> 3
>        c
>
>
>
>
> Thanks in advance..
>
> Regards
> Peter
>

If your table had a db generated sequential unique identifier (an
identity / autoinc), then something along these lines may be what you
are looking for ...

-- Delete everything except the UniqueIDs we want to keep.
DELETE FROM
Table
WHERE
UniqueID NOT IN
(
-- Just get the UniqueIDs we want to keep.
SELECT
UniqueID
FROM
(
-- Get the earlist UniqueID for each Col1, Col2, pairing.
SELECT
Col1,
Col2,
MIN(UniqueID) AS UniqueID
FROM
Table
GROUP BY
Col1,
Col2
)
)

UNTESTED!!!!
From: Richard Quadling on 19 Jul 2010 10:09
On 19 July 2010 15:01, Richard Quadling <rquadling(a)gmail.com> wrote:
> On 19 July 2010 05:44, Peter <peters(a)egrabber.com> wrote:
>> Hi All,
>>
>> I have a  table which contain's some duplicate rows. I just want to delete
>> the duplicate records alone
>> not original records.
>>
>> Assume my table as look as below
>>
>> column1 column2
>> 1
>>        a
>> 1
>>        a
>> 2
>>        b
>> 3
>>        c
>> 3
>>        c
>>
>>
>>
>> i want the above table need  to be as below, After executing the mysql
>> query.
>>
>> column1
>>        column2
>> 1
>>        a
>> 2
>>        b
>> 3
>>        c
>>
>>
>>
>>
>> Thanks in advance..
>>
Slightly more concise ...

-- Delete everything except the UniqueIDs we want to keep.
DELETE FROM
Table
WHERE
UniqueID NOT IN
(
-- Get the earliest UniqueIDs for each Col1, Col2 pairing.
SELECT
MIN(UniqueID)
FROM
Table
GROUP BY
Col1,
Col2
)

http://www.devx.com/tips/Tip/14665




DELETE
Table
FROM
Table T1,
Table T2
WHERE
T1.Col1 = T2.Col1
AND
T1.Col2 = T2.Col2
AND
T1.UniqueID > T2.UniqueID

http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm



etc.

Many different ways.

http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table
Method 3 should be the fastest.
From: Shreyas Agasthya on 19 Jul 2010 10:28
How about this :

CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20));
INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE;

Regards,
Shreyas

On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling <rquadling(a)gmail.com>wrote:

> On 19 July 2010 05:44, Peter <peters(a)egrabber.com> wrote:
> > Hi All,
> >
> > I have a table which contain's some duplicate rows. I just want to
> delete
> > the duplicate records alone
> > not original records.
> >
> > Assume my table as look as below
> >
> > column1 column2
> > 1
> > a
> > 1
> > a
> > 2
> > b
> > 3
> > c
> > 3
> > c
> >
> >
> >
> > i want the above table need to be as below, After executing the mysql
> > query.
> >
> > column1
> > column2
> > 1
> > a
> > 2
> > b
> > 3
> > c
> >
> >
> >
> >
> > Thanks in advance..
> >
> > Regards
> > Peter
> >
>
> If your table had a db generated sequential unique identifier (an
> identity / autoinc), then something along these lines may be what you
> are looking for ...
>
> -- Delete everything except the UniqueIDs we want to keep.
> DELETE FROM
> Table
> WHERE
> UniqueID NOT IN
> (
> -- Just get the UniqueIDs we want to keep.
> SELECT
> UniqueID
> FROM
> (
> -- Get the earlist UniqueID for each Col1, Col2,
> pairing.
> SELECT
> Col1,
> Col2,
> MIN(UniqueID) AS UniqueID
> FROM
> Table
> GROUP BY
> Col1,
> Col2
> )
> )
>
> UNTESTED!!!!
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
Regards,
Shreyas Agasthya
From: Shreyas Agasthya on 19 Jul 2010 14:31
Just to add more perspective :

You will have a table with DISTINCT values.

Drop the initial table (better take a back-up); copy from the temporary
table which will have only DISTINCT values.

Regards,
Shreyas

On Mon, Jul 19, 2010 at 7:58 PM, Shreyas Agasthya <shreyasbr(a)gmail.com>wrote:

> How about this :
>
> CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20));
> INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE;
>
> Regards,
> Shreyas
>
> On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling <rquadling(a)gmail.com>wrote:
>
>> On 19 July 2010 05:44, Peter <peters(a)egrabber.com> wrote:
>> > Hi All,
>> >
>> > I have a table which contain's some duplicate rows. I just want to
>> delete
>> > the duplicate records alone
>> > not original records.
>> >
>> > Assume my table as look as below
>> >
>> > column1 column2
>> > 1
>> > a
>> > 1
>> > a
>> > 2
>> > b
>> > 3
>> > c
>> > 3
>> > c
>> >
>> >
>> >
>> > i want the above table need to be as below, After executing the mysql
>> > query.
>> >
>> > column1
>> > column2
>> > 1
>> > a
>> > 2
>> > b
>> > 3
>> > c
>> >
>> >
>> >
>> >
>> > Thanks in advance..
>> >
>> > Regards
>> > Peter
>> >
>>
>> If your table had a db generated sequential unique identifier (an
>> identity / autoinc), then something along these lines may be what you
>> are looking for ...
>>
>> -- Delete everything except the UniqueIDs we want to keep.
>> DELETE FROM
>> Table
>> WHERE
>> UniqueID NOT IN
>> (
>> -- Just get the UniqueIDs we want to keep.
>> SELECT
>> UniqueID
>> FROM
>> (
>> -- Get the earlist UniqueID for each Col1, Col2,
>> pairing.
>> SELECT
>> Col1,
>> Col2,
>> MIN(UniqueID) AS UniqueID
>> FROM
>> Table
>> GROUP BY
>> Col1,
>> Col2
>> )
>> )
>>
>> UNTESTED!!!!
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
>
> --
> Regards,
> Shreyas Agasthya
>



--
Regards,
Shreyas Agasthya
From: Jim Lucas on 20 Jul 2010 00:37
Peter wrote:
> Hi All,
>
> I have a table which contain's some duplicate rows. I just want to
> delete the duplicate records alone
> not original records.
>
> Assume my table as look as below
>
> column1 column2
> 1
> a
> 1
> a
> 2
> b
> 3
> c
> 3
> c
>
>
>
> i want the above table need to be as below, After executing the mysql
> query.
>
> column1
> column2
> 1
> a
> 2
> b
> 3
> c
>
>
>
>
> Thanks in advance..
>
> Regards
> Peter
>

Use the SQL command alter with the ignore flag.

ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` )

I tested this on my test DB and it worked fine. It erased all the
duplicates and left one instance of the multiple entry values.

This will add a permanent unique restraint to the table. So, you will
never have dupps again.

Jim Lucas
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: functions and global variables
Next: help using phpadmin