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 !!

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 !!!