I think that in most situations, the CASE statement would work just fine. I know that none of my database tables have millions of rows, and never will, and I'm thankful for that. However, I do think that it is important to realize that all of the rows that meet the maze criteria are being reset, even if it's just to the same value. It's always best to understand what's happening in your SQL, even if it isn't a problem.
Has anyone tried using the BEGIN TRAN and COMMIT TRAN syntax within a single CFQUERY tag? I don't see why it wouldn't work, but I've only ever used the CFTRANSACTION method. Thanks! Jon -----Original Message----- From: DURETTE, STEVEN J [mailto:sd1...@att.com] Sent: Friday, February 07, 2014 7:55 AM To: sql Subject: RE: sql query help Yes it would, but if it didn't meet the criteria then it would just leave the value as is. The person asking the question stated that this was for a school, that would probably be a few thousand students at most. Plus I gave the alternative of a table join which would be more restrictive. Steve -----Original Message----- From: Byron Mann [mailto:byronos...@gmail.com] Sent: Thursday, February 06, 2014 11:24 PM To: sql Subject: Re: sql query help Need to update that, the Case statement would update every row where gom = 'maze'. Even those rows not needing to be updated. Byron Mann Lead Engineer & Architect HostMySite.com On Feb 6, 2014 11:17 PM, "Byron Mann" <byronos...@gmail.com> wrote: > Depending on your table size, indexing and number of rows actually being > updated, it may be better to just wrap all the updates into a transaction. > > BEGIN TRAN; > > UPDATE...... > UPDATE...... > UPDATE...... > .... > COMMIT TRAN; > > The case statement example may not be optimal, as it will update every row > unnecessarily. Not nice if you have a million row table and only need to > actually update 30 rows. > > Multiple OR statements is probably better, as you will be restricting the > update to the required rows, but might be hard to read for debugging. Plus > too many ORs and performance may be more optimal with multiple updates. > > I usually find the simple the better. > > Byron Mann > Lead Engineer & Architect > HostMySite.com > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3542 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
