I have the following table structure:
I have to update/replace values:
SET root_id = 4 WHERE root_id = 1
SET root_id = 1 WHERE root_id = 4
SET id = 4 WHERE id = 1
SET id = 1 WHERE id = 4
basically I want:
What is the best way to preform such query?
I would select all ids with matching values and call update query for each. There must be some better solution, with less than 4 queries needed.
I'm using Doctrine ORM and it has some strange implementation of NestedSet structure. Each root node has own root_id and I want to be able to change the order of root nodes, so I decided to use existing root_id column for ORDER_BY instead of making new column for sort order.
Any concerns about this approach, if I wont be accessing elements by ids?
Thanks a lot for help.