Joining a table to itself in MySQL

Joining a table to itself is a technique that, while moderately painful to contemplate, can prove quite valuable in making certain updates possible. Consider an example case where we have a table containing various company names, some of which actually refer to the same company. We need to update the table, setting an ID that will denote where separate names are synonyms. First, to create the table:

1
2
3
4
5
6
7

CREATE TABLE company_names (
name_id INT(10) NOT NULL AUTO_INCREMENT,
company_id INT(10),
name VARCHAR(255) NOT NULL,
SOURCE VARCHAR(100) NOT NULL,
PRIMARY KEY (name_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then to populate it with initial values:

1
2
3
4
5
6
7
8
9

INSERT INTO company_names (company_id, name, SOURCE)
VALUES
(1, "Initech", "source1"),
(NULL, "Initech, Inc.", "source2"),
(NULL, "Initech, USA", "source2"),
(NULL, "Another Company", "source2"),
(NULL, "R4|\/|pant $p4|\/|", "source2"),
(NULL, "Initech", "source3"),
(NULL, "Initech", "source3");

This results in:

Initech from source1 has a company_id, so it has been determined to be a valid company name. We need to update the table, setting the same company_id for other names from other sources, where the names exactly match:

1
2
3
4

UPDATE company_names a
JOIN company_names b ON a.name = b.name
SET a.company_id = b.company_id
WHERE a.company_id IS NULL;

The simple trick is to call the table by one alias, a, then join to it as another alias, b. We then state that the names between the records have to match, and that we want to set the company_id of one to the company_id of the other, where the first doesn’t already have it set. The update results in:

Be Sociable, Share!

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

3 Responses to Joining a table to itself in MySQL

How to Get Six Pack Fast says:

April 15, 2009 at 10:09 am

Hey, nice tips. Perhaps I’ll buy a bottle of beer to the man from that chat who told me to visit your site
Samuel Huckins says:

April 15, 2009 at 4:20 pm

Thanks, glad you found them useful! Conjure me up a bottle while you’re at it
Texas programmer says:

May 15, 2009 at 7:14 am

As a rule I use left join if it’s necessary to combine data of several tables. But in case of the modification of a single table you solution does really works! Thanks.