Move location from one to another

gistfile1.sql

SET @from_loc := 'ChIJ2aunYmllQTQRntXu6lNDlCU';
SET @to_loc := 'ChIJ-yRniZpWPEURE_YRZvj9CRQ';

START TRANSACTION;

# получение начальных данных
SELECT id, path, `level` INTO @from_id, @from_path, @from_level FROM locations WHERE place_id = @from_loc;
SELECT id, path, `level` INTO @to_id, @to_path, @to_level FROM locations WHERE place_id = @to_loc;

SET @new_path := CONCAT(@to_path, @to_id, ',');

# правим саму локацию
UPDATE locations
SET parent_id = @to_id, path = @new_path, `level` = @to_level + 1
WHERE id = @from_id;

SET @child_path_beginning := CONCAT(@from_path, @from_id, ',');
SET @new_child_path_beginning := CONCAT(@new_path, @from_id, ',');
# перебиндиваем детей
UPDATE locations
SET path = REPLACE(path, @child_path_beginning, @new_child_path_beginning), `level` = `level` - @from_level + @to_level + 1
WHERE path <> '' AND path LIKE CONCAT(@child_path_beginning, '%');

COMMIT;