Mysql sequential numbering based on MAX of another column

Question

I've a table lp_pictures which have these fields:

[id | plate_uid]
[1 | aaa]
[1 | aaa]
[1 | aaa]
[1 | bbb]
[1 | bbb]
[1 | bbb]

and i need to UPDATE the field id to becomes:

[id | plate_uid]
[1 | aaa]
[2 | aaa]
[3 | aaa]
[1 | bbb]
[2 | bbb]
[3 | bbb]

The table structure is:

CREATE TABLE `lp_pictures` (
 `id` int(5) NOT NULL,
 `plate_uid` varchar(128) NOT NULL,
 `uid` varchar(64) NOT NULL,
 `filename` varchar(128) NOT NULL,
 `extension` varchar(4) NOT NULL,
 `ip_submitted` varchar(15) NOT NULL,
 `date_submitted` datetime NOT NULL,
 `mode_submitted` varchar(15) NOT NULL,
 `ip_validated` varchar(15) NOT NULL,
 `date_validated` datetime NOT NULL,
 `user_validated` varchar(64) NOT NULL,
 `size` bigint(20) NOT NULL,
 `combination` varchar(9) NOT NULL,
 `type` varchar(15) NOT NULL,
 `oldtimer` tinyint(1) NOT NULL DEFAULT '0',
 `owner` tinyint(1) NOT NULL,
 `name` varchar(128) NOT NULL,
 `email` varchar(128) NOT NULL,
 `notify` tinyint(1) NOT NULL,
 `main` tinyint(1) NOT NULL,
 `source` varchar(512) NOT NULL,
 `comment` text NOT NULL,
 `meaning` text NOT NULL,
 `like` bigint(12) NOT NULL DEFAULT '0',
 `score` bigint(20) NOT NULL,
 `share` bigint(20) NOT NULL DEFAULT '0',
 `visible` tinyint(1) NOT NULL DEFAULT '1',
 `blocked` varchar(1) NOT NULL DEFAULT '0',
 `fb_post_id` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`uid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

The order should be assigned based on date_validated field. (The lower it to the first date).

I need an mysql UPDATE instruction.

Thanks a lot !!

• RolandoMySQLDBA: Give us the exact table structure please : SHOW CREATE TABLE tblname\G
• RolandoMySQLDBA: We need to see SHOW CREATE TABLE lp_picture\G as well

Answer 1

STEP 01 : Create index on date_submitted

ALTER TABLE lp_pictures ADD INDEX (date_submitted);

STEP 02 : Create separate table to order the id values

CREATE TABLE lp_picture_ordering
(
 id INT NOT NULL AUTO_INCREMENT,
 plate_uid varchar(128) NOT NULL,
 uid varchar(64) NOT NULL,
 PRIMARY KEY (plate_uid,id),
 UNIQUE KEY uid (uid)
) ENGINE=MyISAM;

STEP 03 : Load the table ordering info into the ordering table

INSERT INTO lp_picture_ordering (plate_uid,uid)
SELECT plate_uid,uid FROM lp_pictures ORDER BY date_submitted;

STEP 04 : Perform UPDATE JOIN to assign id values

UPDATE lp_pictures A
INNER JOIN lp_picture_ordering B
USING (uid) SET A.id = B.id;

STEP 05 : Drop the ordering table

DROP TABLE lp_picture_ordering;

CAVEATS

CAVEAT #1

You needed to add the date_submitted index so the retrieval ordered by date would be fast

CAVEAT #2

You will only need to do STEP 01 once

CAVEAT #3

STEP 02 creates the increment values for every plate_uid. This technique is only possible with the MyISAM Storage Engine. In my earlier posts, I have suggested using an auto_increment column in a multiple column PRIMARY KEY:

• Apr 21, 2012 : How can you have two auto-incremental columns in one table?
• Jun 10, 2012 : MySQL get next unique value without auto increment
• Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
• Aug 28, 2013 : How to go about modeling the main object of relationship?

CAVEAT #4

In STEP 03, the id values are generated by the Storage Engine.

CAVEAT #5

The ordering table includes uid with a UNIQUE KEY so that STEP 04 can do an INNER JOIN

CAVEAT #6

When your done, no need to keep the ordering table. That's why I drop it.

CAVEAT #7

Should you ever need to do this again, just do Steps 2-5

Give it a Try !!!

• Matbe81: Nice job.. Just a syntax error on STEP 02 block me. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id,plate_uid), UNIQUE KEY uid (uid) ) ENGINE=MyISAM' at line 6
• RolandoMySQLDBA: Forgot the pesky comma. Sorry.
• Matbe81: The result isn't the attended one. As you can see, the numbering should restart at each plate_uid change
• RolandoMySQLDBA: My mistake. I had the lp_picture_ordering of the PRIMARY KEY in the wrong order. I'll reverse it in the above code. Then, try it again. When you complete STEP 03, run SELECT * FROM lp_picture_ordering LIMIT 20; Look at the output. If the ordering is correct there, the order will be correct afterwards.