MySQL: Update a full table, inserting a MD5 hash, for each row a specific one

Question

I added a column to an existing table. Now I need to update the tablecontent by adding a MD5 hash to that new column, based on the content of an existing column.

To be more precise:

id | name | date-of-birth | hash
1  | test | 12.12.12      | NULL

There are more than 1 million rows, where hash = NULL. Now I need to update hash with a MD5 string, that is bases on a corresponding column e.g. name: hash = MD5(test)

I know how to do it for a single row. But how to do that for all rows in a single SQL Statement?

Sometimes it's quicker to google than to post a question... http://dev.mysql.com/doc/refman/5.5/en/update.html — Cylindric, May 04 '12 at 14:21

score 23 · Accepted Answer · answered Nov 19 '11 at 19:10

23

Try this:

UPDATE yourtable
SET hash = MD5(name)
WHERE hash IS NULL

Note that the test is hash IS NULL and not hash = NULL as you wrote in your question.

answered Nov 19 '11 at 19:10

Mark Byers

811,555
193
1,581
1,452

here is my code : UPDATE users SET reference_id = MD5(name.now()) WHERE reference_id IS NULL. Error ! Function name doesn't exists – Ragavendran Ramesh Dec 23 '13 at 05:08
What's the performance of this query? Are there better ways? – noob333 May 24 '16 at 09:58

score 0 · Answer 2 · edited Oct 07 '16 at 07:39

No need write an extra query for update.

Just go to table design
create a new column
select new created column, below you'll get properties
Find for computed Column Specification
Under above opt you'll find formula section write HashBytes([Algo], [ColumnName])

it will automatically create an hash value for rows which are already present.

Check the attached image for more clarification

MySQL: Update a full table, inserting a MD5 hash, for each row a specific one

2 Answers2