CODEWITHSUNDEEP
mysqlsql
Salkz
Salkz

Reputation: 1062

How to get parent id of root for children in a table

Here's what I have (table's name is pobjects):

+----+------------+---------+-----------+------+---------+-----------+
| id | title      | user_id | published | uri  | type_id | parent_id |
+----+------------+---------+-----------+------+---------+-----------+
|  1 | file1.bpmn |       1 |         0 | NULL |       1 |      NULL |
|  2 | file2.bpmn |       1 |         0 | NULL |       1 |      NULL |
|  3 | file3.bpmn |       1 |         0 | NULL |       1 |      NULL |
|  4 | file4.bpmn |       2 |         0 | NULL |       1 |      NULL |
|  5 | root       |       1 |         0 | NULL |       2 |      NULL |
|  6 | root       |       2 |         0 | NULL |       2 |      NULL |
|  7 | root       |       3 |         0 | NULL |       2 |      NULL |
+----+------------+---------+-----------+------+---------+-----------+

These rows represent files and root directories. I need to update each file's parent_id to the same user's root directory id. So, basically I need this result: 

+----+------------+---------+-----------+------+---------+-----------+
| id | title      | user_id | published | uri  | type_id | parent_id |
+----+------------+---------+-----------+------+---------+-----------+
|  1 | file1.bpmn |       1 |         0 | NULL |       1 |         5 |
|  2 | file2.bpmn |       1 |         0 | NULL |       1 |         5 |
|  3 | file3.bpmn |       1 |         0 | NULL |       1 |         5 |
|  4 | file4.bpmn |       2 |         0 | NULL |       1 |         6 |
|  5 | root       |       1 |         0 | NULL |       2 |      NULL |
|  6 | root       |       2 |         0 | NULL |       2 |      NULL |
|  7 | root       |       3 |         0 | NULL |       2 |      NULL |
+----+------------+---------+-----------+------+---------+-----------+

Help would be much obliged as I'm not an SQL guru but I'm tired of not using the possibilities of SQL.

Upvotes: 1

Views: 200

Answers (1)

sagi
sagi

Reputation: 40471

You can do it with UPDATE .. JOIN :

UPDATE YourTable t
JOIN YourTable s
 ON(t.user_id = s.user_id and t.title <> 'root' and s.title = 'root')
SET t.parent_id = s.id

Upvotes: 4

Related Questions