mySql - updating by comparig rows in same table

Question

Hi, I want to update a column by comparing each row to all other rows in the table but I cant figure out how to distinguish the column names in the row being updated with the rows being searched through.

Here's a simplified example...

people:
+--------+-----+----------------+
| name | age | nameClosestAge |
+--------+-----+----------------+
| alice | 20 | |
| bob | 30 | |
| clive | 22 | |
| duncan | 24 | |
+--------+-----+----------------+

To fill in the 'nameClosestAge' column with the name of the person that is closest in age to each person, you could do this...

create temporary table peopleTemp like people;
insert into peopleTemp select * from people;
update people set nameClosestAge = 
(select name from peopleTemp where people.name != peopleTemp.name 
order by abs(people.age - peopleTemp.age) asc limit 1);

Which produces this....
+--------+-----+----------------+
| name | age | nameClosestAge |
+--------+-----+----------------+
| alice | 20 | clive |
| bob | 30 | duncan |
| clive | 22 | alice |
| duncan | 25 | clive |
+--------+-----+----------------+

Surely there is a way to do this without creating a duplicate table. I'm looking for the most efficient method here as I have a very large table and its taking too long to update. I'm usign mySql with PHP. Thanks

Answer 1

You could perform this with just one sub-query and no temp table.

SELECT name, age, (
  SELECT name
  FROM people
  WHERE name != ppl.name
  ORDER BY ABS( people.age - ppl.age ) 
  LIMIT 1
  ) AS nameClosestAge
FROM people AS ppl;

Checked and works :)

EDIT: If you want to be able to work with the calc'ed row, you can use a view;

CREATE VIEW people_close AS 
  SELECT name, age, (
    SELECT name
    FROM people
    WHERE name != ppl.name
    ORDER BY ABS( people.age - ppl.age ) 
    LIMIT 1
  ) AS nameClosestAge
  FROM people AS ppl;

You can't update the calculated field but can query against it easily.