现在要将表里面的同tid的记录的那些排序位置进行一次排列,
涉及的表只有一个url,表的设计如下:
mysql> desc url;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| url_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| url | varchar(255) | NO | | | |
| tid | int(10) unsigned | NO | | 0 | |
| ordernum | int(10) unsigned | NO | | 1 | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
部分数据如下:
mysql> select * from url limit 0,20;
+--------+------------------------------------+-----+----------+
| url_id | url | tid | ordernum |
+--------+------------------------------------+-----+----------+
| 3 | http://ww3.azom.c.rcc/A4VV2y2w.htm | 3 | 1 |
| 4 | http://ww3.azom.c.rcc/Bl5L0gtL.htm | 3 | 1 |
| 5 | http://ww3.azom.c.rcc/GDFxMBFf.htm | 3 | 1 |
| 6 | http://ww3.azom.c.rcc/68meXZ69.htm | 3 | 1 |
| 7 | http://ww3.azom.c.rcc/cnXV8nld.htm | 5 | 1 |
| 8 | http://ww3.azom.c.rcc/fd3fvyV1.htm | 2 | 1 |
| 9 | http://ww3.azom.c.rcc/dL1Xx5iN.htm | 5 | 1 |
| 10 | http://ww3.azom.c.rcc/7bQovB0X.htm | 4 | 1 |
| 11 | http://ww3.azom.c.rcc/mjVaz5Nz.htm | 3 | 1 |
| 12 | http://ww3.azom.c.rcc/WcV0C4cA.htm | 3 | 1 |
| 13 | http://ww3.azom.c.rcc/poCmA3Ka.htm | 3 | 1 |
| 14 | http://ww3.azom.c.rcc/2mZdt2tb.htm | 3 | 1 |
| 15 | http://ww3.azom.c.rcc/kRZtmMZr.htm | 1 | 1 |
| 16 | http://ww3.azom.c.rcc/G7yRb71U.htm | 4 | 1 |
| 17 | http://ww3.azom.c.rcc/0ByPUK0p.htm | 3 | 1 |
| 18 | http://ww3.azom.c.rcc/6raRp6p6.htm | 3 | 1 |
| 19 | http://ww3.azom.c.rcc/FPC8t9DT.htm | 3 | 1 |
| 20 | http://ww3.azom.c.rcc/z7XT7xQ6.htm | 3 | 1 |
| 21 | http://ww3.azom.c.rcc/z9Q2r6sh.htm | 4 | 1 |
| 22 | http://ww3.azom.c.rcc/pSKS98NQ.htm | 3 | 1 |
+--------+------------------------------------+-----+----------+
20 rows in set (0.28 sec)
其中ordernum的就是同tid里面的不同的url_id在这个里面排的位置。
如何才可以使用一个SQL语句进行批量更新他们,让他们自动排呢?我使用的SQL语句:
mysql> UPDATE url u1 SET ordernum = ( SELECT COUNT( u2.url_id ) +1
-> FROM url u2
-> WHERE u2.url_id > u1.url_id
-> AND u1.tid = u2.tid
-> GROUP BY u2.tid
-> LIMIT 0 , 1 ) ;
ERROR 1093 (HY000): You can't specify target table 'u1' for update in FROM claus
e
如何才可以正确实现啊?
涉及的表只有一个url,表的设计如下:
mysql> desc url;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| url_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| url | varchar(255) | NO | | | |
| tid | int(10) unsigned | NO | | 0 | |
| ordernum | int(10) unsigned | NO | | 1 | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
部分数据如下:
mysql> select * from url limit 0,20;
+--------+------------------------------------+-----+----------+
| url_id | url | tid | ordernum |
+--------+------------------------------------+-----+----------+
| 3 | http://ww3.azom.c.rcc/A4VV2y2w.htm | 3 | 1 |
| 4 | http://ww3.azom.c.rcc/Bl5L0gtL.htm | 3 | 1 |
| 5 | http://ww3.azom.c.rcc/GDFxMBFf.htm | 3 | 1 |
| 6 | http://ww3.azom.c.rcc/68meXZ69.htm | 3 | 1 |
| 7 | http://ww3.azom.c.rcc/cnXV8nld.htm | 5 | 1 |
| 8 | http://ww3.azom.c.rcc/fd3fvyV1.htm | 2 | 1 |
| 9 | http://ww3.azom.c.rcc/dL1Xx5iN.htm | 5 | 1 |
| 10 | http://ww3.azom.c.rcc/7bQovB0X.htm | 4 | 1 |
| 11 | http://ww3.azom.c.rcc/mjVaz5Nz.htm | 3 | 1 |
| 12 | http://ww3.azom.c.rcc/WcV0C4cA.htm | 3 | 1 |
| 13 | http://ww3.azom.c.rcc/poCmA3Ka.htm | 3 | 1 |
| 14 | http://ww3.azom.c.rcc/2mZdt2tb.htm | 3 | 1 |
| 15 | http://ww3.azom.c.rcc/kRZtmMZr.htm | 1 | 1 |
| 16 | http://ww3.azom.c.rcc/G7yRb71U.htm | 4 | 1 |
| 17 | http://ww3.azom.c.rcc/0ByPUK0p.htm | 3 | 1 |
| 18 | http://ww3.azom.c.rcc/6raRp6p6.htm | 3 | 1 |
| 19 | http://ww3.azom.c.rcc/FPC8t9DT.htm | 3 | 1 |
| 20 | http://ww3.azom.c.rcc/z7XT7xQ6.htm | 3 | 1 |
| 21 | http://ww3.azom.c.rcc/z9Q2r6sh.htm | 4 | 1 |
| 22 | http://ww3.azom.c.rcc/pSKS98NQ.htm | 3 | 1 |
+--------+------------------------------------+-----+----------+
20 rows in set (0.28 sec)
其中ordernum的就是同tid里面的不同的url_id在这个里面排的位置。
如何才可以使用一个SQL语句进行批量更新他们,让他们自动排呢?我使用的SQL语句:
mysql> UPDATE url u1 SET ordernum = ( SELECT COUNT( u2.url_id ) +1
-> FROM url u2
-> WHERE u2.url_id > u1.url_id
-> AND u1.tid = u2.tid
-> GROUP BY u2.tid
-> LIMIT 0 , 1 ) ;
ERROR 1093 (HY000): You can't specify target table 'u1' for update in FROM claus
e
如何才可以正确实现啊?
select a.url_id,count(*) as cnt
from url a inner join b on a.tid=b.tid and a.url_id<=b.url_id
group by a.url_id) u2 on u1.url_id=u2.url_id
set u1.ordernum=u2.cnt
(
select a.url_id,count(*) as px from url a inner join url b
on a.tid=b.tid and a.url_id>=b.url_id group by a.url_id) c
on u1.url_id = c.url_id
SET ordernum = c.px