Updating a MySQL table with a self-referencing column

Question

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

show a proper sample of the result you want obtain – ScaisEdge Jul 23 '16 at 08:59 — ScaisEdge, Jul 23 '16 at 08:59

ScaisEdge · Answer 1 · 2020-10-18T19:05:06.703

1

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`;

edited Oct 18 '20 at 19:05

answered Jul 23 '16 at 09:06

ScaisEdge

131,976
10
91
107

actually, it's the other way around, but i really couldn't have done it without you. update places as a inner join places as b set b.`selfreference` = a.`id` where b.`selfreference-name` = a.`name` – Tench Jul 23 '16 at 09:43
I know this is old, but the first query doesn't look right. If I had to guess, I'd remove the "set" line and add a "b" after "as" – Mordechai Oct 18 '20 at 19:04
@Mordechai .. correct .. thanks .. answer updated removing the worng set .. – ScaisEdge Oct 18 '20 at 19:05

score 0 · Answer 2 · answered Jul 23 '16 at 13:43

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

Updating a MySQL table with a self-referencing column

2 Answers2