I use mysql and php with phpmyadmin. I have a major problem with partition based counters that I would like to improve, but my SQL knowledge prevents me from doing so. I'm very distressed by this.
I would like to have a counter for duplicate data in my table, if this value gets a duplicate value it will add a number after the value and start over from 1 until it encounters a new value and so on. Here's how the final result should look:
--------------------------- 1 | Josh-1 --------------------------- 2 | Josh-2 -------------------------- 3 | Josh-3 -------------------------- 4 | Josh-4 -------------------------- 5 | Fred-1 -------------------------- 6 | Fred-2 -------------------------- 7 | Fred-3 -------------------------
I've gotten help here before with this counter, but it didn't work the way I wanted it to. And, when I press the insert button in the form, after reloading phpmyadmin, the form looks like this:
--------------------------- 1 | Josh-1-1-1 --------------------------- 2 | Josh-2 -------------------------- 3 | Josh-3 -------------------------- 4 | Josh-4 -------------------------- 5 | Fred-1 -------------------------- 6 | Fred-2 -------------------------- 7 | Fred -------------------------
What's going on here? The code I'm asking for help rewriting is this:
UPDATE usermeta u1, (SELECT u1.`id`, CONCAT(u1.`name`,'-',ROW_NUMBER() OVER(PARTITION BY u1.`name` ORDER BY u1.`id`)) newname FROM usermeta u1 JOIN (SELECT `name` , COUNT(*) FROM usermeta GROUP BY `name` HAVING COUNT(*) > 1) u2 ON u1.`name` = u2.`name` ) u3 SET u1.`name` = u3.`newname` WHERE u1.`id` = u3.`id`
Can this code be rewritten to create a table with numbered names and duplicates that looks like the first table example and works fine in phpmyadmin? All help is greatly appreciated. Please keep in mind that I am a troubled intermediate sql user.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Possible solution - BEFORE INSERT trigger and additional MyISAM table with secondary autoincrement:
Working table
Additional table
Trigger
Insert rows - the AI index is added to the name. Check the result.
Look what is stored in additional table now.
db<>fiddle here
If your working table
userexists already, and it contains some data, then you'd create additional table and fill it with data using, for example,CREATE TABLE user_index (id INT AUTO_INCREMENT, name VARCHAR(127), PRIMARY KEY (name, id)) ENGINE=MyISAM SELECT MAX(SUBSTRING_INDEX(name, '-', -1) + 0) id, SUBSTRING_INDEX(name, '-', 1) name FROM user GROUP BY 2;https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=38f028cfe1c9e85188ab0454463dcd78