In MySQL, I have a table named Channel (Auto-generated by Prisma ORM)

CREATE TABLE `Channel` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `guildId` INTEGER UNSIGNED NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `description` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `position` INTEGER NOT NULL,
    `parentId` INTEGER NULL,
    `ratelimit` INTEGER NOT NULL DEFAULT 0,
    `type` ENUM('textChannel', 'categoryChannel') NOT NULL,

    INDEX `Channel_guildId_idx`(`guildId`),
    UNIQUE INDEX `Channel_guildId_name_key`(`guildId`, `name`),
    UNIQUE INDEX `Channel_guildId_position_key`(`guildId`, `position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

As you can see, there's a UNIQUE INDEX constraint Channel_guildId_position_key

How can I do something that doesn't break this unique constraint such that: (I can get all the rows with the same guild_id with SELECT * FROM Channel WHERE guildId = $guildId)

update a channel current's position field value to x (assuming x is bigger than current.position) update all channels between current.position and position x and decrement their position field by 1 update current.position to position x

Basically, I want to move a channel to a position and everything in between new position and old position needs to advance forward. But I'm getting blocked by the constraint I set which makes (position, guildId) a unique identifier, and thus requiring a temporary value to accomplish this task. I'm looking for a one-operation solution that doesn't break the constraint

Note: This includes (possibly) more than two rows, and a single swap won't work.

If you still do not understand the question, think of chat channels, when client drags a channel to a different position I want to update all the positions of the channels that are "in between" the "before" and "after" positions.

Example: Here the table has 3 rows

INSERT INTO Channel (id, guildId, name, position, type) VALUES (1, 1, 'ch1', 1, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (2, 1, 'ch2', 2, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (3, 1, 'ch3', 3, 'textChannel');

Now, let's say I want to move channel 'ch3' to position 1, then 'ch1' and 'ch2''s position would be changed to 2, 3 respectively. But if I run these operations: (Assuming I got the IDs already)

$guildId = 0

$originalPositionOfChannel3 = 3

$newPosition = 0

UPDATE Channel SET position = $newPosition WHERE guildId = $guildId AND name = 'ch3';
UPDATE Channel SET position = position + 1 WHERE guildId = $guildId AND position < $originalPositionOfChannel3 AND position >= $newPosition;```