How do I modify the hundards of records based on my query result?

Question

Hi, I inserted some error record to my table, I can query via the sql below and found hundards of record marked with half.

SELECT * FROM `marathon`
WHERE gender = 'male' && distance = 'half';

How to write a SQL then I can modify the result rows distance from 'half' to 'full'.

Thank you.

Answer 1

update marathon set distance = 'full' WHERE gender = 'male' and distance = 'half';

read http://dev.mysql.com/doc/refman/5.0/en/update.html (mysql update syntax)

Answer 2

Thierry has the right answer, but I want share a technique I use when writing update statements:

update m
set distance = 'full'
--select marathon_id, gender, distance from  
From marathon m
WHERE gender = 'male' and distance = 'half';

By embedding the select in the stamenet as a comment, you can check to see which records will be affected before running the update and if you are updating to a complex formula you can even see what the current and updated results would be. Even better if you already have the select statment, it's pretty easy to add the update parts on top of it. This also helps you see how your update relates directly to your select.