Menu
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?
658
The MYSQL MD5 function takes any string and returns an MD5 128-bit checksum for that string. MD5 (str); MD5 Hash of an individual row in MySQL To hash each row in a query return, you can concatenate the data into a string and return it as a hash using a combination of the MD5 function and CONCAT function.
Summary: updating data is one of the most important tasks when you work with the database. In this tutorial, you will learn how to use the MySQL UPDATE statement to update data in a table. The UPDATE statement updates data in a table. It allows you to change the values in one or more columns of a single row or multiple rows.
Using MySQL UPDATE to update rows returned by a SELECT statement. You can supply the values for the SET clause from a SELECT statement that queries data from other tables. For example, in the customers table, some customers do not have any sale representative.
Here is the query to update all entries with md5 version of name: mysql> update DemoTable1887 set HashPassword=md5 (Password); Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
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.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
