----- Original Message -----
From: "Rich" <lists@stripped>
Sent: Saturday, March 18, 2006 6:28 PM
Subject: Update Multiple Records
> Hi folks.
> I want to set the status of 5 records to 'completed'. how do I go about
> that without having to prepare 5 different instructions?
> "update myTable set status = 'completed' where id=10 OR id=20 OR id=30 OR
> id=40 OR id=50"
That should work. So should:
update myTable set status = 'completed' where id in (10, 20, 30, 40, 50)
and (assuming id 10 through 50 are the ONLY numbers in that range!), so
update myTable set status = 'completed' where id between 10 and 50
A properly written program would probably use one or more variables to
control which rows got deleted; for example, something like this pseudocode:
update myTable set status = 'completed' where id between :lowValue and
where lowValue and highValue are variables. Your program would then set the
values of the two variables before executing the UPDATE statement. But you
didn't say if you were using a programming language to do some of the work
or if you were trying to do everything purely in MySQL.
> I'm trying to reduce the number of instructions.
The best way to accomplish this is to reason out the circumstances under
which you will do updates. What will you know about the row(s) being updated
when it is time to update them? Be careful to make sure you are updating
ONLY the rows that should be updated. If you specify your WHERE conditions
incorrectly, you can update every row of the table or none at all.
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006