如果其他值重复,如何更新MySQL表中的值?

我有具有以下结构的表:

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)
上一篇:numpy and pandas


下一篇:如何在mysql中插入多个值并避免重复