Mysql replace multi-column values

Databases
1

Hello.

I have the following table structure:

id    root_id
1     1
2     1
3     1
4     4
5     4
6     4

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:

id    root_id
4     4
2     4
3     4
1     1
5     1
6     1

Q1:
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.

Q2:
Concept consideration:
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.

2

If you set a value to 4 where it is currently 1 then, if you set all 4’s to 1 as was your other requirement, they will all end up as 1.

Better, imv, to set value to 1000 where value = 1. then set value = 1 where value = 4. then set value = 4 where value = 1000.

I hope I understood your question.

3

if you did it in two steps, yes

however, if you did them both at the same time in a single update statement, mysql updates each row independently, so you could change all 1’s to 4’s and all 4’s to 1’s at the same time

read up on it in da manual, it’s really neat

:slight_smile:

4

thanks rudy,

I didn’t know that so i went for the easier option, not to break my data.
bazz

5

Hey,
The following syntax will give me duplicate primary error:

UPDATE table
  SET id CASE id
  WHEN 1 THEN 2
  WHEN 2 THEN 1
  END

Do you have any idea how to pass it, without changing the properties of id column?

Thanks

6

thank you so much for testing it

it turns out that i was wrong with my information

yes, i know, shocking, but it does happen occasionally (not since last year, though)

you’ll have to use multiple update statements

7

Do you have any idea how to set first (temporary) id to be ensured its unique so that you don’t mess up records or need separated select query?