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.

2
  • 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
2

Join table to itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z  ON z.name = w.name
                  AND z.id > w.id
;
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;
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`);
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;
-3
DELETE FROM MyTable
FROM         MyTable LEFT OUTER JOIN
    (SELECT     MIN(RowId) as RowId, Col1, Col2, Col3
     FROM         MyTable
     GROUP BY Col1, Col2, Col3) AS KeepRows ON 
     MyTable.RowId = KeepRows.RowId
WHERE     (KeepRows.RowId IS NULL) 
4
  • 1
    Please explain how this works. – Colin 't Hart Feb 15 at 8:56
  • Comments are not for answers. Please edit your answer to add more information about how it works. – Colin 't Hart Jun 30 at 10:20
  • first, you have to select your rows to be kept (SELECT MIN(RowId) as RowId, Col1, Col2, Col3 FROM MyTable GROUP BY Col1, Col2, Col3) AS KeepRows then left outer join your table with the keepRows to get all rows from your table and the rows from keep table then delete the row whos has a null value for keeprows try it its work fine for me – Hamza Yahia Jun 30 at 11:16
  • As I said before, please edit your answer! – Colin 't Hart Jun 30 at 11:17

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.