Cool MySQL tricks

It's been a while since I've written a blog post, and I figured this was a good topic to write about. For the most part, I don't use SQL. There's numerous ORM (Object-Relational Mapping) tools and frameworks out there (ie Hibernate) that abstract away all the boiler-plate CRUD code. When I do have to use SQL (be it on the command line or with a query browser), I'm not afraid to dive right in, and it's interesting to learn new things about SQL because there's so much to learn and it's such a ridiculously powerful query language.

I had to update the configuration in one of my databases, but the configuration required inserting numerous new lines into a table for user roles. I could've written a stored procedure to do it, but given my (admittedly somewhat limited) knowledge of SQL, I figured this would take too much time and I didn't want to do it. (This is what I like to call constructive laziness). I kept Googling and found MySQL's INSERT INTO ... SELECT syntax, which allows you to conveniently insert the results of one query into another table. I'm sure that most other database implementations out there have a similar convenience syntax, but I happen to work with MySQL so there you go. It took what would've been a numerous line script with looping and separate SELECT and INSERT statements down to a one liner. If you use MySQL (especially with LAMP / AJAX) I recommend that you read about it.