CODEWITHSUNDEEP
mysqlsqldatabase
Hirad Roshandel
Hirad Roshandel

Reputation: 2187

MYSQL Updating row to maximum value of similar rows

I have a table like this in MYSQL:

ID   |   NAME    |  VALUE  |
----------------------------
 1   |   Bob     |  1      |
 2   |   Bob     |  2      |
 3   |   Jack    |  5      |
 4   |   Jack    |  8      |
 5   |   Jack    |  10     |

and I'm trying to update the VALUE column to the highest value of rows with same NAME. So the result should be:

ID   |   NAME    |  VALUE  |
----------------------------
 1   |   Bob     |  2      |
 2   |   Bob     |  2      |
 3   |   Jack    |  10     |
 4   |   Jack    |  10     |
 5   |   Jack    |  10     |

I managed to get the max value like this:

SELECT MAX(Value) max FROM `table` GROUP BY Name having count(*) >1 AND MAX(Value) != MIN(Value)

But can't figure out how to put it in my update

Update table set Value = (SELECT MAX(Value) max FROM `table` GROUP BY Name having count(*) >1 AND MAX(Value) != MIN(Value))

Doesn't work. I'd appreciate any help.

Upvotes: 2

Views: 1048

Answers (5)

Bill Karwin
Bill Karwin

Reputation: 562701

This is easier than other answers are making it.

UPDATE MyTable AS t1 INNER JOIN MyTable AS t2 USING (Name)
SET Value = GREATEST(t1.Value, t2.Value);

You don't have to find the largest value. You just have to join each row to the set of rows with the same name, and set the Value to the greater Value of the two joined rows. This is a no-op on some rows, but it will apply to every row in turn.

Upvotes: 2

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/f79a3/1

UPDATE t1 
INNER JOIN (SELECT name, MAX(`value`) max_value
           FROM t1 GROUP BY name) t2 
ON t1.name = t2.name
SET t1.value = t2.max_value;

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11413

You may use this query. The table is joined with a subquery (table t2) that contains the results you want to update your table with:

UPDATE `table` t1, 
   (SELECT Name, MAX(Value) maxv, MIN(Value) minv
    FROM `table`
    GROUP BY Name
    HAVING COUNT(*)>1 AND maxv != minv) t2
SET t1.Value = t2.maxv
WHERE t1.Name = t2.Name;

If you want to know how will the values be updated, you can first run an equivalent SELECT query:

SELECT t1.*, t2.maxv
FROM `table` t1, 
   (SELECT Name, MAX(Value) maxv, MIN(Value) minv
    FROM `table`
    GROUP BY Name
    HAVING COUNT(*)>1 AND maxv != minv) t2
WHERE t1.Name = t2.Name;

This query will display all the fields of table, followed by the new value maxv. You can check the current value and the new value, and if it looks fine, you may run the UPDATE query.

Upvotes: 1

G.Arima
G.Arima

Reputation: 1171

Simple left join would do the trick.
Try this out and let me know in case of any queries. 

select a.id,a.name,b.value
from
table a
left join
(select name,max(value) as value from table group by name) b
on a.name=b.name;

Upvotes: 0

Satyadev
Satyadev

Reputation: 643

Create a temporary table consisting of ID NAME and MAX VALUE as follows:

CREATE TEMP TABLE TABLE1 AS 
(SELECT NAME,MAX(Value) value FROM `table` GROUP BY Name having count(*) >1 
AND MAX(Value) != MIN(Value)
);

Use this temporary table to do your update as follows:

UPDATE
Table_A
SET
    Table_A.value = Table_B.value
FROM
    `table` AS Table_A
    INNER JOIN TABLE1 AS Table_B
    ON Table_A.NAME = Table_B.NAME

Also this code is somewhat of an approximation as i am not familiar with mysql but i am familiar with sql.

Let me know if this doesn't help.

Upvotes: 0

Related Questions