Convert Column to MD(5)

[jsnull]

I have column I'd like to make MD(5) from MySQL Admin ... so an sql something like

"UPDATE `thistable` SET `column1` = md5(`column1`)"

is what I am looking for. However this syntax fails. Suggestions?

Jim Null

http://www.jimnull.com

 

[elsenorjose]

You're trying to change the column name:

ALTER TABLE `thistable` CHANGE COLUMN `column1` `md5`;

If you're trying to set the value of column1 to md5, then:

UPDATE `thistable` SET `thistable`.`column1` = `md5`
WHERE `thistable`.`column1` = `currentvalue`

 

[jsnull]

Thanx for your reply.

I'm wanting to change the current text in the column to be a MD5 formatted value.

Jim Null

http://www.jimnull.com

 

[towerbase]

Jim, when you say this syntax fails you have not told us the exact error message produced by the failure. Usually, but not always, the error message provides a good indication of what is wrong.

It would also help to give us a description of the table. What kind of column is column1? An MD5 checksum consists of 32 hexadecimal digits. Is the data type of column1 suitable to receive 32 hexadecimal digits?

Incidentally, there is no need to put tick marks around names such as thistable or column1 unless the names contain spaces. I hope that these are not the names you are using in your real application ....

Andrew
Hampshire, UK

 

[jsnull]

Howdies ... I basically have a varchar column, lets say it is named myname. I want to change the contents of this column to md5.

Jim Null

http://www.jimnull.com

 

[guelphdad]

your code in the first table is correct, though I would use SHA1 instead of MD5 as MD5 has been hacked.

 

[jsnull]

Everyone ... thank you for your feedback. With the details each of you have provided I altered the code to this:

UPDATE thistable SET `column1` = md5(`column1`)

and it worked perfectly. I removed the ` from around the table name.

Jim Null

http://www.jimnull.com

 

[guelphdad]

you never need to use the backticks unless you are using a reserved word as the name of a database, table or column. in that case you are always better to rename it rather than have to resort to using the backtick which is non-standard.

any reason you are set on using MD5 when it isn't secure?