How to update values in MySQL table if other values are duplicates?
I have a table with the following structure:
tbl1
COL1 COL2 COL2
---- ---- ----
A B 1
B C 3
A C 11
A D 13
B D 10
How to update col3 if the values in col1 are duplicates?
I want the values in col3 to update with the largest one found.
The resulting table looks like this:
COL1 COL2 COL2
---- ---- ----
A B 13
B C 10
A C 13
A D 13
B D 10
Thanks in advance!
source to share
With an update combined with the desired data. Correlated subqueries are not good :
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)
source to share
First load 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 col3 value for any given col1.
Example
For col1 = A you have different values 1, 11 and 13.13 is the highest
For col1 = B you have different values 3 and 10.10 is the highest
You will need a subquery that will find the highest col3 value for any given col1.
Here is this request:
SELECT col1,MAX(col3) maxcol3
FROM TBL1 GROUP BY col1;
Let's run this 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 the whole table and update col3 whenever col1 of the subquery is the same as col1 of the table. Here is this request:
UPDATE
(
SELECT col1,MAX(col3) maxcol3
FROM TBL1 GROUP BY col1
) A
INNER JOIN TBL1 B USING (col1)
SET B.col3 = A.maxcol3;
Run this UPDATE JOIN query and select all 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 the 5 rows are the same but only 3 changes stems from the fact that rows that have (col1, col3) ("A", 13) and ("B", 10) already have maximum values and 't need to change.
source to share