How do I update first record in a table using mysql?

Question

I have an unknown amount of records in the 'room_photos' table. There is a column named 'main_photo' and currently each of the records has a value of 'no' for that column. I would like to create a sql statement that updates the first record in the table and changes the value of the 'main_photo' column to 'yes'.

So this is what I have now:

TABLE room_photos
photo_id   |   main_photo
51               |   no
52               |   no
53               |   no

And this is what I need:

TABLE room_photos
photo_id   |   main_photo
51               |   yes
52               |   no
53               |   no

Answer 1

Use LIMIT

UPDATE tablename SET main_photo = 'yes' LIMIT 1;

The above query will assume that the first record in the table, regardless of the value of photo_id, will be updated. If you want the record with the lowest ID to be updated use ORDER BY, too:

UPDATE tablename SET main_photo = 'yes' ORDER BY photo_id ASC LIMIT 1;