Using UPDATE Function To Mass Update Records

Question

Due to an incident a few months prior, thousands of author_ids have been deleted from my forum database and I am looking to restore them. Because thousands of posts have no author_id, the original author of the post has been greyed out along with the Guest_ prefix.

What the forums currently looks like.

What the forums should look like.

Anyway, here is an image of the columns the Posts table consists of. And here is an image of the indexes.

My solution:

UPDATE posts
SET author_id = 375, author_name = 'Peter'
WHERE author_name = 'petersmileyface'

The above was what I had tried, but when I execute the code all the author_ids were changed to 375, thus every post had an author_name of 'Peter'. w3schools states that only if I omit the WHERE clause, all the records will be updated.

Why does my solution not work? Thank you in advanced for your answers.

Answer 1

Your request isn't wrong, you must have missed something during execution.

For mass update, you can use another kind of request, with a CASE statement, try it, maybe you won't have problems anymore...

UPDATE posts 
SET 
   author_id = 
      CASE author_name 
         WHEN 'petersmileyface' THEN 375
         WHEN 'portugaltrollface' THEN 412
      END,
   author_name = 
      CASE author_name 
         WHEN 'petersmileyface' THEN 'Peter'
         WHEN 'portugaltrollface' THEN 'Pedro'
      END;