WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue DESC) MaxRN,
ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue ASC) MinRN
FROM Items
)
SELECT
CategoryId,
MIN(CASE WHEN MaxRN = 1 THEN ItemId END) IdMaxValue,
MIN(CASE WHEN MinRN = 1 THEN ItemId END) IdMinValue
FROM CTE
GROUP BY CategoryId
CREATE TABLE im_goods_new (
id int(10) unsigned zerofill NOT NULL auto_increment,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO im_goods_new
SELECT DISTINCT name_goods
FROM im_goods;
INSERT INTO im_goods_sections (id_goods, id_section)
SELECT DISTINCT new.id, old.id_section
FROM im_goods_new new
JOIN im_goods old
ON new.name = old.name_goods;
DROP TABLE im_goods;
RENAME TABLE im_goods_new TO im_goods;UPDATE table
SET field = CONCAT('456', SUBSTRING(field, 4))
WHERE field LIKE '123%';
UPDATE table SET field = REPLACE(field, '123', '456');