CREATE TABLE inventory_player (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
item_id int(11) NOT NULL,
slot_id int(11) NOT NULL,
deleted_at_time datetime DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO inventory_player VALUES
(1, 4, 0, 0, NULL),
(2, 4, 0, 1, NULL),
(3, 4, 0, 2, NULL),
(4, 4, 0, 3, NULL),
(5, 4, 1, 4, NULL),
(6, 4, 1, 4, NULL),
(7, 4, 1, 4, NULL),
(8, 4, 1, 4, NULL),
(9, 4, 1, 4, NULL);
SELECT * FROM inventory_player order by id;
SET @ids := null;
WITH
old as (
SELECT
*,
DENSE_RANK() OVER (order by item_id DESC) - 1 dr,
ROW_NUMBER() OVER (PARTITION BY item_id order by id) rn
FROM
inventory_player
),
jn as (
SELECT
o1.id, o1.user_id, o1.item_id, o2.slot_id
FROM
old o1
INNER JOIN old o2 ON o2.dr = o1.item_id and o1.rn = o2.rn
)
UPDATE inventory_player ip
INNER JOIN jn ON jn.id = ip.id
SET ip.slot_id = jn.slot_id
WHERE (SELECT @ids := CONCAT_WS(',', ip.id, @ids));
DELETE FROM inventory_player where id in (@ids);
SELECT * FROM inventory_player order by id;
Telegram: DB developers channel