CODEWITHSUNDEEP
mysql
Abhay
Abhay

Reputation: 11

Updating multiple rows with same value in mysql

I have a table t with columns id(primary key),a,b,c,d. assume that the columns id,a,b and c are already populated. I want to set column d =md5(concat(b,c)). Now the issue is that this table contains millions of records and the unique combination of b and c is only a few thousands. I want to save the time required for computing md5 of same values. Is there a way in which I can update multiple rows of this table with the same value without computing the md5 again, something like this:

update t set d=md5(concat(b,c)) group by b,c;

As group by does not work with update statement.

Upvotes: 1

Views: 926

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Create a temp table:

CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable 
AS (SELECT b, c, md5(concat(b, c)) as d FROM t group by b, c)

Update initial table:

UPDATE t orig 
JOIN tmpTable tmp ON orig.b = tmp.b AND orig.c = tmp.c
SET orig.d = tmp.d

Drop the temp table:

DROP TABLE tmpTable

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One method is a join:

update t join
       (select md5(concat(b, c)) as val
        from table t
        group by b, c
       ) tt
       on t.b = tt.b and t.c = tt.c
     set d = val;

However, it is quite possible that any working with the data would take longer than the md5() function, so doing the update directly could be feasible.

EDIT:

Actually, updating the entire table is likely to take time, just for the updates and logging. I would suggest that you create another table entirely for the b/c/d values and join in the values when you need them.

Upvotes: 1

Related Questions