SQL Fun
time to read 2 min | 389 words

Recently I've been dealing with a lot of SQL code, and it's has been very interesting to see all the myriad of ways that you can botch a query J.  One thing that I encountered is the tendecy to keep thinking procedurally instead of set-base. A lot of people seems to be famliar with inserting from select, like this:

(This will duplicate all the blogs in the table, not very useful in this instance, admittedly, but very useful elsewhere):

insert into Blogs

(

      blog name

)

select blog name

      from Blogs

 

But not a lot of people seem to be familiar with the Update counterpart for this statement. Let's take an example, I'm hosting a blogging server, and I found out that some of my users where using nasty words in their blog's name. Therefor, I decided that I want to set all the blogs' names to <User Name>'s Blog. How do I do that? The code that I've seen lately opens a cursor, and iterate over each and every one of the rows, update each in turn.  This is procedural thinking, and usually ineffiecnt. It's much better to do it via a set base command, like this one:

 

update Blogs

set blog name = u.user name + '''s Blog'

from Users u, UsersBlogs ub

where u.user id = ub.user id and

      Blogs.blog id = ub.blog id

 

This command even hanldes a many-to-many relationship between users and blogs (it uses the one of the blog's owners). It's not as obvious, and it's a bit harder to discover, but I think it is better. It was actually quite fun to re-write it this way, it made it much easier to read and understand. I spares you the reams of code that you would need to write this command using cursors.

Tweet Share Share 0 comments