Populate (UPDATE) parent id's based on previous rows?

Question

In my database there is a table of categories. In order to select those categories hierarchically, I'm looking for a way to populate the parent id's in the junction table (shown below). Right now, only the parent id's for the sort_order 1 rows are already populated.

+----------------------+
|category_relationships|
+----------------------+
+----+-------------+-----------------+-----------+----------------+
| id | link_id[FK] | category_id[FK] | parent_id[FK] | sort_order |
+----+-------------+-----------------+-----------+----------------+
| 1       2             1                 1               1       |
| 2       2             133               1               2       |
| 3       3             2                 2               1       |
| 4       3             200               2               2       |
| 5       3             333               200             3       |
| 6       4             1                 1               1       |
| 7       5             3                 3               1       |
| 8       5             222               3               2       |
| 9       5             223               222             3       |
| 10      5             456               223             4       |
+-----------------------------------------------------------------+

The sample data above is what I'm looking to do. As you can see, all the rows have the link_id in common. That is the column which separates the category groups. But I also need to use the sort_order column to determine how each parent_id is related and it's order. This is the way I've tried, but it doesn't update any rows.

UPDATE category_relationships SET parent_id=
(
SELECT category_id FROM 
  (
  SELECT link_id, category_id, sort_order 
  FROM category_relationships
  ) AS t1 
WHERE t1.link_id = category_relationships.link_id
AND t1.sort_order = 1
)
WHERE sort_order = 2;

This approach won't do them all at once, but that's ok, I can just populate them one level at a time.

Answer 1

I was able to solve this problem using a SELF JOIN.

UPDATE category_relationships a 
LEFT JOIN category_relationships b ON
(a.link_id = b.link_id AND b.sort_order = 1) <--source row
SET a.parent_id = b.category_id
WHERE a.sort_order = 2; <--destination row

Essentially what this does, is puts the original table and a different version next to each other with the second table having the data specified in the JOIN condition. Then the UPDATE statement uses the second table as a reference to add the record.