我有具有以下结构的表:
TBL1
COL1 COL2 COL2
---- ---- ----
A B 1
B C 3
A C 11
A D 13
B D 10
如果col1中的值重复,如何更新col3?
我想用发现的最大值更新col3中的值.
结果表如下所示:
COL1 COL2 COL2
---- ---- ----
A B 13
B C 10
A C 13
A D 13
B D 10
提前致谢 !!!
解决方法:
随着更新与所需的数据. Correlated subqueries are not wellcome:
update T inner
join ( select c1, max( c3) as m from T) T2
on T.c1 = T2.c1
set T.c3 = T2.m;
经过测试:
mysql> create table T ( c1 char(1), c3 int ) ;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into T values ( 'A', 1),('B',3),('A',11),('A',13);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * From T;
+------+------+
| c1 | c3 |
+------+------+
| A | 1 |
| B | 3 |
| A | 11 |
| A | 13 |
+------+------+
mysql> update T inner join ( select c1, max( c3) as m from T) T2
on T.c1 = T2.c1 set T.c3 = T2.m;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> select * from T;
+------+------+
| c1 | c3 |
+------+------+
| A | 13 |
| B | 3 |
| A | 13 |
| A | 13 |
+------+------+
4 rows in set (0.00 sec)