xQgxQ9Dh

1DROP TABLE IF EXISTS `scores`;
2
3CREATE TABLE `scores` (
4  `id` int(11) NOT NULL AUTO_INCREMENT,
5  `value` char(1) DEFAULT NULL,
6  `level` int(11) DEFAULT NULL,
7  PRIMARY KEY (`id`)
8) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
9
10LOCK TABLES `scores` WRITE;
11INSERT INTO `scores` (`id`,`value`,`level`)
12VALUES
13	(1,'B',NULL),
14	(2,'A',NULL),
15	(3,'A',NULL),
16	(4,'C',NULL),
17	(5,'A',NULL);
18UNLOCK TABLES;
19
20/* Update level value of all A scores */
21SET @rownum=0; 
22UPDATE scores t, (SELECT @rownum:=@rownum+1 rownum, scores.* FROM scores WHERE value='A') r  
23SET t.level = r.rownum WHERE (t.id = r.id);
24
25/* 
26NOW scores is:
27
28id     value    level
29----------------------
301	B	NULL
312	A	1
323	A	2
334	C	NULL
345	A	3
35
36*/