Updating a range of records with MySQL

January 8, 2016, 10:44 pm
Author: James Griffiths

Updating a single record entry in a MySQL database table is fairly simple, but what if you need to update a batch of records with one single query?

It's not as difficult as you might think...

Using your table's primary key as the field for determining the ranges to update within/outside of you can write a query similar to the following (substituting the table/field names and range values listed below with your own):

UPDATE `business_details` SET `business_area` = "London/Greater London", `opening_hours` = "Mon - Sat (10AM - 6PM)" WHERE `location_id` BETWEEN 35 AND 50;

If you're using a database management system such as phpMyAdmin you can simply use the above query in the SQL tab (again paying attention to replace the table/field names and values with your own - and don't forgot to use the backtick character for 'escaping' those table/field names).

Alternatively the above can be modified to be used in a PHP (or similar) script and directly from the MySQL command line itself (if your hosting account provides such a facility).

Nice and easy, huh?

Categories

« Return to Posts

Post a comment

All comments are welcome and the rules are simple - be nice and do NOT engage in trolling, spamming, abusiveness or illegal behaviour. If you fail to observe these rules you will be permanently banned from being able to comment.