0

My table looks like:

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
|  5 | uska    |
+----+---------+

I want it to change it to

+----+---------+
| id | name    |
+----+---------+
|  1 | uska    |
|  2 | chinese |
|  3 | bakshi  |
|  4 | jj      |
+----+---------+

modify changes in the same table without switching to another table.

I tried many ways, many hrs to delete or select but doesn't work, I did use code to resolve this, but I will appreciate for any explanation, thank you.

I tried in two ways like

  1. delete from t where id<>(select id from t group by name);

Here I am getting an error as

Table 't' is specified twice, both as a target for 'DELETE' and as a separate source for data"

  1. delete t1.* from t t1,t t2 where 1.id<>t2.id and t1.name=t2.name;

2 duplicate rows are getting deleted instead of one.

  • I think your question has solution here – Dinesh Kumar Aug 27 '18 at 16:43
  • How many rows in the table? What percentage is dups? Are there other columns? – Rick James Sep 24 '18 at 22:37
1

Join table to itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z  ON z.name = w.name
                  AND z.id > w.id
;
|improve this answer|||||
0

The best option is to extract desired records to a temporary table, then delete the main table and then import records from temporary to the main table.

First, create your temporary table inserting not duplicated record:

CREATE TABLE newtable SELECT * FROM oldtable GROUP BY name;

Then delete old table with duplicated records

TRUNCATE TABLE oldtable;

Then import data from temporary table to main table.

INSERT INTO oldtable (id,name)
SELECT id,name FROM newtable;

Then, if you want, you can drop the temporary table.

DROP TABLE newtable;
|improve this answer|||||
0

You can create unique index on the field name

1) Drop id field

ALTER TABLE table_name DROP COLUMN `id`;

2) Make name field unique, note "ignore" which will ignore duplicate values

ALTER IGNORE  TABLE  `table_name` ADD UNIQUE(`name`); 

3) Add id field back

ALTER TABLE `table_name` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
|improve this answer|||||
0

Yet another approach. However, this assumes you don't need id for any other purpose.

ALTER TABLE t
    DROP PRIMARY KEY,
    DROP COLUMN id;
ALTER TABLE IGNORE t
    ADD PRIMARY KEY (name);

And another approach. This keeps the AUTO_INCREMENT id, and it works "best" when there is a high percentage of duplicates and there are no other columns.

CREATE TABLE new
    name VARCHAR...,
    id INT UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY(name),
    INDEX(id);   -- to keep AUTO_INCREMENT happy
INSERT INTO new
    SELECT DISTINCT name FROM t;
RENAME TABLE  t TO old,  new TO t;  -- atomic and fast
DROP TABLE old;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.