How to delete duplicated fields from two different tables in MySQL

Question

Imagine that you have two tables.

Table bar:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Table foo:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| name_alternative | varchar(255) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

And you want to remove the rows from table bar that have the same name value as the field name_alternative from table foo

I could use the following query:

DELETE FROM foo WHERE name IN (SELECT name_alternative FROM bar);

But this takes a very long time to execute having a large amount of records in both tables.

So I was wondering if there is a better alternative to this query.

score 1 · Accepted Answer · 2013-02-28 13:39:13Z

1

This is another way that would work, and I think it would be more efficient:

DELETE FROM bar
    USING foo, bar
    WHERE name_alternative = name

Here is a working SQLFiddle example.

See the documentation for delete.

edited Feb 28, 2013 at 13:39

answered Feb 28, 2013 at 11:23

user1919238

Sign up to request clarification or add additional context in comments.

3 Comments

rfc1484 Over a year ago

This generates the following error message: ERROR 1109 (42S02): Unknown table 'bar' in MULTI DELETE

rfc1484 Over a year ago

I think this is the correct answer: DELETE FROM bar USING foo, bar WHERE name_alternative = name; could you correct it in order to mark your answer as accepted?

user1919238 Over a year ago

@rfc1484, sorry for the typo. It is corrected now and has a working example.

Déjà vu · Accepted Answer · 2013-02-28 11:31:23Z

1

Besides @dan1{4} answer, my guess is that it takes a long time because there is no index on bar(name).

You don't need an index covering the whole 255 chars, depending on your strings, ie if the first characters are different on a lot of rows, create an index on a few characters

CREATE INDEX indexname ON bar(name(8))

8 is a suggested value that you may want to refine. The index will help mysql to locate each bar row that matches a foo.namealternative

answered Feb 28, 2013 at 11:31

Déjà vu

29k6 gold badges79 silver badges105 bronze badges

Comments

Clodoaldo Neto · Accepted Answer · 2013-02-28 11:30:24Z

0

delete from bar
using (
    select foo.name_alternative
    from 
        bar
        inner join
        foo on bar.name = foo.name_alternative
) s
where bar.name = s.name_alternative

answered Feb 28, 2013 at 11:30

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

How to delete duplicated fields from two different tables in MySQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related