Literal Tables and Updates with Joins in SQL

Posted by Daniel Lyons on November 13, 2008

Most SQL users are aware of the word VALUES in the INSERT statement:

INSERT INTO valid_languages (name, age) 
        VALUES ('Lisp', 50)

Most of my programming career, I was under the misapprehension that this was just the syntax of the INSERT statement. It isn’t. I learned a few years ago that this is just as valid:

INSERT INTO valid_languages (name, age)
        (SELECT language_name, age 
         FROM languages 
         WHERE age > 20 AND lispy = true)

But somehow it didn’t really connect for me that VALUES isn’t really part of INSERT and you can use it other places. For example, you can make literal tables:

SELECT * 
        FROM VALUES 
        ('Lisp', 50, true), 
        ('Scheme', 30, true), 
        ('Clojure', 1, true) 
        AS languages (name, age, lispy)
name age lispy
‘Lisp’ 50 true
‘Scheme’ 30 true
‘Clojure’ 1 true

This is a table like any other; you can do whatever you want with it.

Another thing I’ve been sort of generally aware of but not really used much is joins within UPDATE. You can do pretty much whatever you can do with a SELECT in an UPDATE, as long as you eventually assign a column to a value on the main table of the UPDATE.

I was thinking about how to deal with the N+1 problem when resorting the rows in a particular table. Usually, you write code that looks something like this in Ruby:


        params[:ids].split(',').each_with_index |id, index|
          SomeRecord.find(id).update_attribute(:index, index)
        end

This sucks, for a number of reasons, but the really big one is that as your list gets bigger you’re going to execute more SQL statements. SQL is great, you should always be able to accomplish one task with one statement (assuming you’re using a valid RDBMS).

As it turns out, you can combine the VALUES trick from above with an UPDATE and a join to hit the database once in this scenario:

UPDATE content
        SET index = new.index
        FROM 
          (VALUES (1, 4), (2, 3), (13, 2), (16, 1)) 
          AS new (id, index)
        WHERE content.id = new.id;

All you have to do now in your code is generate the VALUES tuples. In Ruby, it would look like this (untested):

values = '(' + [1,2,3].zip([14,15,16]).collect { |a, b| "(#{a}, #{b})" }.join(',') + ')'
        sql = "UPDATE content 
               SET index = new.index 
               FROM (VALUES #{values}) AS new (id, index) 
               WHERE content.id = new.id"
        Content.connection.execute sql