List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 18 2006 11:47pm
Subject:Re: Update Multiple Records
View as plain text  
----- Original Message ----- 
From: "Rich" <lists@stripped>
To: <mysql@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 
should this:

    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 
:highValue

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.

--
Rhino 



-- 
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

Thread
Update Multiple RecordsRich19 Mar
  • RE: Update Multiple RecordsDaevid Vincent19 Mar
  • Re: Update Multiple RecordsRhino19 Mar