updating mysql columns retrospectively using data from row

Question

We have just changed some of the functionality in one our web apps, and every row in one of tables needs to have new data added, the data is 8 character hash of the name value and the current timestamp.

I am trying to update 2 columns as we also need to swap to columns around,

    UPDATE projects 
SET uri_hash = (SELECT slug FROM projects WHERE id = 79), 
    slug = MD5(SHA1(CONCAT(
        CURRENT_TIMESTAMP, (
            SELECT name FROM projects WHERE id=79
        )
    ))

What I am doing wrong, basically I want the values of uri_hash to equal the slug column and then I want to MD5 and SHA1 encrypt the current timestamp and the name value and assign that to the slug.

Answer 1

If you look at the point #4 in this answer, and assuming id is your primary key in your table, the following should work:

UPDATE projects p1, projects p2 
SET p1.uri_hash = p1.slug, p1.slug=MD5(SHA1(CONCAT(CURRENT_TIMESTAMP,p2.name) 
WHERE p1.id=p2.id;

Note, this will update all the rows of your table.