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!
You can select the maximum value from COL3 in the subquery and execute 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'
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)
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.