How to change data in multiple rows of SQL query?

Question

SQL

Hello.
There is a table like this:

id  | paren_id | left_key | righ_key | name
1   |  0       | 1        | 2        | категория 1
1   |  1       | 5        | 6        | категория 2
1   |  1       | 3        | 4        | категория 3
1   |  0       | 7        | 8        | категория 4

left_key external sorting
righ_key internal sorting
After the query I get

category 1
--category 3
--category 2
category 4

How to build an SQL query when changing the category position?
If "category3" becomes higher than "category 1", then you need to change the data in all columns left_key , righ_key to change to new ones.

The idea is this:
I send the id of the category and before_id to the function (which one should be before)
I get the data left_key , righ_key of all rows. I'm working on new positions.

How now to change them in the table?
Do one big query to the DB or is there another solution? Or tell me in which direction to dig.
Sorry if I'm being too smart.

Answer 1

I use transactions..
You can write as many requests as you like, combine them into a sequence and send.
The transaction engine will lock changes, execute all requests, and roll back all changes if something goes wrong.
PDO