Ralf Marmorglatt Ralf Marmorglatt - 2 months ago 16
SQL Question

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

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 collumn 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?

Answer

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.