Copy-Down in SQL

April 14, 2009 10:36 PM

Is there a GUI SQL editor that emulates a spreadsheet-style "copy down"? If not, is there a technique I should learn?

I seem to spend 10-15 mins a day touching up some mysql data in a huge remote CRM-type database (I don't have shell access) and I notice that I spend most of that time copy-pasting the same corrections down one column for 50 or 60 rows. (I do a select to find new stuff, eyeball it, and find that one of the records has the right info and the other fifty need to be made the same, or none are right and I need to change them all).

If I was using a spreadsheet like Excel, that would take 2 seconds (drag down a range, control-d duplicate), but I haven't found a GUI SQL editor that can do this, so I click-copy-paste-click for 15 mins. This is time-wastey and feels silly. There's got to be an easier way.

(I realize that technically the app would have to do 60 individual updates, but that would be behind the scenes while I move on to more fun parts of my day, right?)

So is there a GUI SQL app that emulates the "copy down" of spreadsheets? Or if I were more clueful, is there a stored statement or "macro" I could make in one of these things that would take the selected field in the current record and dupe it down down down? If so, kick me in the right direction: I'm no database geek but I'm not wholly useless technically, either. I can probably learn from a few examples. (15 mins a day times 200 days a year is enough time to be worth reading a book!)

I'd prefer OSX but I would use Windows or Linux for a few minutes per day if the right tool is out there. For OSX, I've tried out the official mysql client, RazorSQL, Navicat, SequelPro, and SQLgrinder. Some are better than others at the copy-paste and ad hoc correcting I do, but none have "duplicate down".

These seems simple enough (to my pretty little head) that I must be missing something.

posted by rokusan to technology (10 comments total)

In SQuirreL, it's possible to select a column, and paste that as a comma separated IN list.

I'd select the table's id column for all rows I wanted to change, and paste that to the end of

"update tablename set column = value where id in" (paste) ";"

to get "update tablename set column = value where id in (1, 4, 7, 1729) ;"

And then execute that single statement.

Advantages: no copying down, one update statement that updates one column's values instead of sixty updates that may be updating the values in all columns.
posted by orthogonality at 10:47 PM on April 14

Most SQL GUIs have a query editor. You should be able to do a SQL UPDATE if you can isolate the rows you need to change with a query. Is this not what you want to do?
posted by devilsbrigade at 10:49 PM on April 14

Once you've got the SELECT worked out, you should be able to just

UPDATE table SET field = 'value' WHERE whatever_you_already_had;

...unless I too am missing something.
posted by pompomtom at 10:51 PM on April 14

I confuse. I meant "selected" as in "highlighted in the GUI" in my question... I don't know how/if that maps to actual SQL SELECTs. I do see a scary query editor command line thingie, devilsbrigade, though I'm afraid to try much there. :)

So forget the GUI for a second I guess... are there two general-type queries I can use that will do this?

1) select all records in table "inbound" where field "phoneNo" contains "(212)".
2) in all those records, change field "region" from whatever it is (various) to "Manhattan"

I still think I should be able to click-select and control-D... what is wrong with programmers? :)
posted by rokusan at 11:24 PM on April 14

UPDATE inbound SET region = 'Manhattan' WHERE phoneNo LIKE '%(212)%';
posted by nicwolff at 11:40 PM on April 14

Great. GUIs are for sissies anyway, right?

You were all right, though it took nicwolff's sample (which just worked as-is) to make my slow brain finally click. I now see that I can save a bunch of stored queries like that example as "Favorite Queries" in the app, and then that's a single menu away.

I'm now querifying all my most-common corrections so I should be able to perform them in seconds instead of minutes. Hopefully, I'll have myself completely automated and obsoleted soon and can sleep in more often.

I love you all.
posted by rokusan at 12:14 AM on April 15

Head hurts.
posted by orthogonality at 12:53 AM on April 15

My stupidity is boundless, ortho. ;)
posted by rokusan at 12:55 AM on April 15

Q: How can you make the common man respect your computer science knowledge?
A: Replace their job with a SQL stored procedure
posted by spatula at 2:51 AM on April 15 [2 favorites]

GUIs are for sissies anyway, right?

No, just for noobs.

People who want to get moderately complicated things done quickly and easily will sooner or later realize that the CLI is the literate way to operate a computer.
posted by flabdablet at 7:16 AM on April 15