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

I have table with following structure :

我有以下结构的表格:

TBL1

TBL1

COL1 COL2 COL2
---- ---- ----
A    B    1
B    C    3
A    C    11
A    D    13
B    D    10

How can I update col3 If values in col1 are duplicates ?

如果col1中的值是重复的，如何更新col3 ?

I want the values in col3 be updated with the largest found.

我想让col3中的值更新为最大的值。

Тhe resulting table to look like:

Тhe结果表看起来像:

COL1 COL2 COL2
---- ---- ----
A    B    13
B    C    10
A    C    13
A    D    13
B    D    10

Thanks in advance !!!

提前谢谢! ! !

3 个解决方案

#1

With an update joined with desired data. 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;

Tested:

测试:

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)

#2

You can select the highest value from COL3 in a subquery and do an update query on your table with the value from the subquery

您可以在子查询中从COL3中选择最高的值，并使用子查询中的值对表进行更新查询

UPDATE TBL1 SET COL3 = (SELECT COL3 FROM TBL1 WHERE COL1 = 'A' ORDER BY COL3 DESC LIMIT 0,1) AS a WHERE COL1 = 'A'

#3

First, let's load your sample data

首先，让我们加载示例数据

mysql> drop database if exists dilyan_kn;
Query OK, 1 row affected (0.04 sec)

mysql> create database dilyan_kn;
Query OK, 1 row affected (0.00 sec)

mysql> use dilyan_kn
Database changed
mysql> create table TBL1
    -> (col1 char(1),col2 char(1),col3 int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into TBL1 values
    -> ( 'A' , 'B' ,  1 ),
    -> ( 'B' , 'C' ,  3 ),
    -> ( 'A' , 'C' , 11 ),
    -> ( 'A' , 'D' , 13 ),
    -> ( 'B' , 'D' , 10 );
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from TBL1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| A    | B    |    1 |
| B    | C    |    3 |
| A    | C    |   11 |
| A    | D    |   13 |
| B    | D    |   10 |
+------+------+------+
5 rows in set (0.00 sec)

mysql>

Looking at your desired output in the question, it looks like you want the highest value of col3 for any given col1.

查看问题中所需的输出，看起来您希望对任何给定的col1使用col3的最高值。

Example

例子

For col1 = A, you have distinct values 1, 11, and 13. 13 is the highest

对于col1 = A，有不同的值1、11和13。13是最高的

For col1 = B, you have distinct values 3 and 10. 10 is the highest

对于col1 = B，有不同的值3和10。10是最高的

You will need a subquery that finds the highest value of col3 for any given col1.

您将需要一个子查询来为任何给定的col1查找col3的最高值。

Here is that query:

这里查询:

SELECT col1,MAX(col3) maxcol3
FROM TBL1 GROUP BY col1;

Let's run that subquery

让我们运行子查询

mysql> SELECT col1,MAX(col3) maxcol3
    -> FROM TBL1 GROUP BY col1;
+------+---------+
| col1 | maxcol3 |
+------+---------+
| A    |      13 |
| B    |      10 |
+------+---------+
2 rows in set (0.00 sec)

mysql>

Let's use this subquery to JOIN against the whole table and update the col3 column whenever the col1 column of the subquery matches the col1 column of the table. Here is that query:

让我们使用这个子查询对整个表进行连接，并在子查询的col1列与表的col1列匹配时更新col3列。这里查询:

UPDATE
(
    SELECT col1,MAX(col3) maxcol3
    FROM TBL1 GROUP BY col1
) A
INNER JOIN TBL1 B USING (col1)
SET B.col3 = A.maxcol3;

Let's run that UPDATE JOIN query and SELECT all of TBL1

让我们运行UPDATE JOIN查询并选择所有的TBL1

mysql> UPDATE
    -> (
    ->     SELECT col1,MAX(col3) maxcol3
    ->     FROM TBL1 GROUP BY col1
    -> ) A
    -> INNER JOIN TBL1 B USING (col1)
    -> SET B.col3 = A.maxcol3;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 5  Changed: 3  Warnings: 0

mysql> select * from TBL1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| A    | B    |   13 |
| B    | C    |   10 |
| A    | C    |   13 |
| A    | D    |   13 |
| B    | D    |   10 |
+------+------+------+
5 rows in set (0.00 sec)

mysql>

Mission Accomplished !!!

任务完成! ! !

The reason why 5 rows matched but only 3 changed stems from the fact that the rows that have (col1,col3) being ('A',13) and ('B',10) already have the max values and don't need to be changed.

5行匹配但只有3行更改的原因，是因为具有(col1、col3)的行('A'、13)和('B'、10)已经具有最大值，不需要修改。

#1