Well that's a different question! ;) Given a set of values, iterating through and updating the values in the original table would also work fine, eliminating the use of a temp table. As to whether that's more efficient - hard to say. Will depend on the size and structure of your original table, as well as the number of entries you're changing.
Updates/replaces of single rows by unique keys are generally fairly inexpensive and quick. Doing the select back from the temp table could cause a table lock for a while, depending on number of rows. Upside is it could be transaction compliant. In your case if 'appname' is a unique value and indexed as such, it should be quick to update one record at a time. Looping through hundreds of records and updating each make take time but should keep the database reasonable responsive for other queries while that is happening. Downside is concurrency issues while your updates are occuring. Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote:
thanks for the reply dan... but is there a better/more efficent way...>???? thanks -----Original Message----- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, August 11, 2006 8:13 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: replace/temp tbl question... Bruce, seems like this ought to work just fine. Dan On 8/11/06, bruce <[EMAIL PROTECTED]> wrote: > hi... > > can someone tell me if this should work in theory... > > basically, i'm looking to get a bunch of rows from my initial table, based > on the value of a given column/field. i'd then like to do a complete > replace/update on the values in the temp table. finally, i'd like to write > everything in the replaced/updated tmp table back to the orignal tbl... > > i'm essentially using 3 steps to get this accomplished... > > create tmp table temp > select a.appName, a.universityID > from appTBL a > where a.complete = 0 > > replace into temp > (appName, universityID) > values(%s,%s) > > replace into appTBL > select t.appName, t.universityID > from temp t > > > thoughts/comments/etc... > > -thanks > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
