1

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

  • Give us the exact table structure please : SHOW CREATE TABLE tblname\G – RolandoMySQLDBA May 19 '14 at 19:00
  • We need to see SHOW CREATE TABLE lp_picture\G as well – RolandoMySQLDBA May 19 '14 at 21:52
0

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:

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

  • 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 – Matbe81 May 20 '14 at 14:25
  • Forgot the pesky comma. Sorry. – RolandoMySQLDBA May 20 '14 at 14:36
  • The result isn't the attended one. As you can see, the numbering should restart at each plate_uid change – Matbe81 May 20 '14 at 15:03
  • 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. – RolandoMySQLDBA May 20 '14 at 20:29
  • It works perfectly. Thank you VERY MUCH !! – Matbe81 May 20 '14 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.