CODEWITHSUNDEEP
mysqlself-referenceself-referencing-table
Tench
Tench

Reputation: 527

Updating a MySQL table with a self-referencing column

I have a table (simplified) that looks like this:

id     | name   | selfreference | selfreference-name
------ | -------| --------------| ------------------ 
1      | Vienna |               |  
2      | Wien   |               | Vienna
3      | Виена  |               | Vienna

The selfreference column refers to the id numbers of the same table. In the above example, both Wien and Виена refer to the same city, so the value of their selfreference column should be equal to 1.

In other words, I need to do something like

 update `places` 
 set `places`.`selfreference` = 
 (select `places`.`id` from `places`where `places`.`name` = `places`.`selfreference-name`)

but the SELECT statement above is obviously wrong. I am at a loss how to proceed.

Any tips would be greatly appreciated.

All best, Tench

Edit: the desired output would look like this:

id     | name   | selfreference | selfreference-name
------ | -------| --------------| ------------------ 
1      | Vienna |               |  
2      | Wien   |  1            | Vienna
3      | Виена  |  1            | Vienna

Upvotes: 2

Views: 1607

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133390

Could be you need a self join

chekc with select

select a.*, b.*
from  `places` as a
inner join `places` as b

where b.`name` = a.`selfreference-name`;

and then if the query above give you the right result

update `places` as a
inner join `places` as b
set b.`selfreference` =  ab.`id`
where b.`name` = a.`selfreference-name`;

Upvotes: 1

1000111
1000111

Reputation: 13519

The following query does the job:

UPDATE places p1
INNER JOIN places p2 ON p1.`name` = p2.`selfreference-name`
SET p2.selfreference = p1.id;

p2 -> instance of table places which will be updated.

p1 -> instance of table places from where the id of the matching selfreference-name is taken.

WORKING DEMO BEFORE UPDATING

WORKING DEMO AFTER UPDATING

Upvotes: 0

Related Questions