How can I update values in MySQL table If other values are duplicates?

Question

I have table with following structure :

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 ?

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

Тhe resulting table to look like:

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

Thanks in advance !!!

Answer 1

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.

Example

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

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

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

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:

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

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.

Answer 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

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

Answer 3

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)