Wish: SUB-INSERT SQL statements

Sub-selects are nice:

INSERT INTO person
 (name, phone, city_id)
VALUES
 ( 
   'john',
   '555-1234',
   (SELECT id FROM city WHERE name = 'Los Angeles')
 )

It saves a DB roundtrip to find the city.id for ‘Los Angeles’.

But sometimes you encounter a new city and first have to assign an entry for it in the city table. Except that the row may already exist, so first you have to select the city, if it does not exist, create the city and finally store the person. MySQL has a nice pattern for storing rows that might already exist in the DB, allowing us to combine the select-if-not-found-store steps into one:

INSERT INTO city
  (name)
VALUES
  ('Palo Alto')
ON DUPLICATE KEY
  UPDATE id = LAST_INSERT_ID(id);

MySQL will return the correct city.id for the new city entry regardless of whether the row had to be inserted or it already existed.

Now, it would be sweet if INSERTs could be used the same way SUB-SELECTs are, allowing us to combine all three steps into a single statement:

INSERT INTO person
 (name, phone, city_id)
VALUES
  ( 
   'john',
   '555-1234',
   (
     INSERT INTO city
       (name)
     VALUES
      ('Palo Alto')
     ON DUPLICATE KEY
      UPDATE id = LAST_INSERT_ID(id);
   )
 )

This pattern happens all the time when storing complex records that span multiple tables. It would be very convenient if databases would support it natively.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: