Page 1 of 1

UPDATE of LOTS of entries at one time

Posted: Mon Jan 15, 2007 2:47 pm
by seodevhead
Hey guys,

I have a db application that displays to a user a number of records from one table in a database. The number of records can vary from hundreds to thousands at one-time. And each record has a little checkbox next to it and if the user wants to delete any record, he/she just puts a checkmark next to each of the "bad" records. After marking off all the "bad" entries... the user clicks the submit button at the bottom and all those marked records are to be UPDATE'd in a certain column telling the database these records are marked as "bad" (not actually deleted though).

Problem is... I have never really done something like this and am not sure how to make the UDPATE query work with something like this.

I know all the PK id's for each of the records the user marked before hitting the submit button... but how do I go about structuring my UPDATE query to change the good/bad ENUM column in the records table for each of the pk id's that were checked off? For instance...

Code: Select all

UPDATE table1 SET status='Bad' WHERE id=45 OR id=76 OR id=234 OR id=829 OR id=1827 OR id=9283
Am I on the right track here? The query could be VERY LONG considering the user could check off hundreds of records at a time. Any help or guidance on this matter is greatly appreciated.

Posted: Mon Jan 15, 2007 2:55 pm
by jayshields
You could do what you have suggested, of you could use the IN clause, like this:

Code: Select all

UPDATE 
  `table1` 
SET 
  `STATUS` = 'Bad' 
WHERE 
  `id`
IN (
44,
45,
96,
323
)
Not sure which query will be faster, but from what I can tell, if you're bothered about speed of query execution, you shouldn't be allowing the user to change that amount of records at once. Why not paginate your results and allow the same technique for the query?

Also, the [ syntax ] tags suggest that STATUS might be a reserved word...