And then there is the (ugly) semi-manual way

Post #332,623 by CRConrad 9/15/10 8:07:41 PM Reply	And then there is the (ugly) semi-manual way OK, I know this is half a year old and solved already, "done and dusted". Just thought I'd contribute this other technique that can be useful in all situations like this: Run the Select first, written to give you a bunch of single-line update statements; then take the output from your select and run that. Something like, say: `select 'update subscribers set IP = ' \|\| IP \|\| ' where email = ' \|\| email \|\| ' and list_ID = ''foo'';' from subscribers where IP = 'bar' and listID = 'baz';` Here's the "(semi-)manual" bit: Now copy the lines from your SQL output window into your SQL input window and click "Run as a script" (or whatever your equivalent is), and you're done. Heck, you can make it even more manual if you want to: Having trouble getting those apostrophes/single-quotes right around the "foo" bit? Just write "&%#@" in stead of ''''foo''' in the select statement, run it, then paste the whole resulting shebang into a text editor and do a global search-and-replace (replace "&%#@" with "'foo'"), before pasting the corrected result into your SQL command window and running it. Not for the Crazed One's 500 M rows, of course, but for anything up to a few thousand...? Heck, yeah -- what you lose in execution time is more than made up for by the coding time you save by simplifying the task this way. It's "Old-school hot-rodding applied to SQL RDBMS": It's an ugly kludge, but it works, and it's fast. :-) -- Christian R. Conrad Same old username (as above), same old GMail.
Post #332,624 by static 9/15/10 8:14:45 PM Reply	This is also good for invasive updates. You know the sort: lots of rows to change and no downtime available. The advantage is that lots of small UPDATE statements won't prevent the site running normally. Wade. Q:Is it proper to eat cheeseburgers with your fingers? A:No, the fingers should be eaten separately.
Post #332,630 by drook 9/15/10 9:12:21 PM Reply	That's my kind of ugly I've done more maintenance that way than I care to remember. (And obviously more than I did remember at the time I was asking.) -- Drew
Post #332,632 by static 9/15/10 9:17:00 PM Reply	It's also an interesting lesson in pragmatism. Whilst it has it's own kind of elegance, a lot of purist programmers will cringe at this solution. They would prefer Leveraging The Power Of SQL directly, which ignores, however, the real-world issue that the database can't be unavailable for an hour whilst your big, clever query runs to completion... Wade. Q:Is it proper to eat cheeseburgers with your fingers? A:No, the fingers should be eaten separately.
Post #332,635 by drook 9/15/10 9:27:26 PM Reply	I've done round trips mySQL -> vim -> PHP -> mySQL Run a query and grab the output. Search/replace in vim. Save as a PHP script that issues updates in batches of n. I've done this to move sites to new servers when the respective mySQL boxes couldn't talk to each other. -- Drew
Post #332,641 by jay 9/15/10 11:25:34 PM Reply	Another batching method One thing I've done when I had to update a lot of live records and I wasn't concerned with rolling them back is to create an update that works in little chunks. Use whatever option your SQL system has to limit the number of rows updated/deleted, such as top in TSQL. Then stick the whole thing in a while loop that does the SQL, sleeps for a few seconds and then loops as long as their are any rows to process. Here is one I used to delete out all the detail records for one very large contract from a live system. Slightly altered to protect the innocent. The loop breaks when it runs out of records because @@ROWCOUNT records how many records the last update/insert/delete affected in TSQL. While (1=1) Begin delete top (10000) from spot where contract_id = 1234; if @@ROWCOUNT = 0 break; -- wait 10 second between loops to relieve congestion on the server WAITFOR DELAY '00:00:10'; End Not exactly the same problem. I did this because doing a delete more then a million rows at once was just clogging up the transaction log and slowing everything to crawl. However, with a bit of string manipulation and exec you could use this solution to scale your method up to the really big problems. Jay

Welcome to IWETHEY!