Monday, June 29, 2009

Approaches to "UPSERT"

This week in the Database Programmer we look at something called an "UPSERT", the strange trick where an insert command may magically convert itself into an update if a row already exists with the provided key. This trick is very useful in a variety of cases. This week we will see its basic use, and next week we will see how the same idea can be used to materialize summary tables efficiently.

An UPSERT or ON DUPLICATE KEY...

The idea behind an UPSERT is simple. The client issues an INSERT command. If a row already exists with the given primary key, then instead of throwing a key violation error, it takes the non-key values and updates the row.

This is one of those strange (and very unusual) cases where MySQL actually supports something you will not find in all of the other more mature databases. So if you are using MySQL, you do not need to do anything special to make an UPSERT. You just add the term "ON DUPLICATE KEY UPDATE" to the INSERT statement:

insert into table (a,c,b) values (1,2,3)
    on duplicate key update
     b = 2,
     c = 3

The MySQL command gives you the flexibility to specify different operation on UPDATE versus INSERT, but with that flexibility comes the requirement that the UPDATE clause completely restates the operation.

With the MySQL command there are also various considerations for AUTO_INCREMENT columns and multiple unique keys. You can read more at the MySQL page for the INSERT ... ON DUPLICATE KEY UPDATE feature.

A Note About MS SQL Server 2008

MS SQL Server introduced something like UPSERT in SQL Server 2008. It uses the MERGE command, which is a bit hairy, check it out in this nice tutorial.

Coding a Simpler UPSERT

Let us say that we want a simpler UPSERT, where you do not have to mess with SQL Server's MERGE or rewrite the entire command as in MySQL. This can be done with triggers.

To illustrate, consider a shopping cart with a natural key of ORDER_ID and SKU. I want simple application code that does not have to figure out if it needs to do an INSERT or UPDATE, and can always happily do INSERTs, knowing they will be converted to updates if the line is already there. In other words, I want simple application code that just keeps issuing commands like this:

INSERT INTO ORDERLINES
       (order_id,sku,qty)
VALUES 
       (1234,'ABC',5)

We can accomplish this by a trigger. The trigger must occur before the action, and it must redirect the action to an UPDATE if necessary. Let us look at examples for MySQL, Postgres, and SQL Server.

A MySQL Trigger

Alas, MySQL giveth, and MySQL taketh away. You cannot code your own UPSERT in MySQL because of an extremely severe limitation in MySQL trigger rules. A MySQL trigger may not affect a row in a table different from the row originally affected by the command that fired the trigger. A MySQL trigger attempting to create a new row may not affect a different row.

Note: I may be wrong about this. This limitation has bitten me on several features that I would like to provide for MySQL. I am actually hoping this limitation will not apply for UPSERTs because the new row does not yet exist, but I have not had a chance yet to try.

A Postgres Trigger

The Postgres trigger example is pretty simple, hopefully the logic is self-explanatory. As with all code samples, I did this off the top of my head, you may need to fix a syntax error or two.

CREATE OR REPLACE FUNCTION orderlines_insert_before_F()
RETURNS TRIGGER
 AS $BODY$
DECLARE
    result INTEGER; 
BEGIN
    SET SEARCH_PATH TO PUBLIC;
    
    -- Find out if there is a row
    result = (select count(*) from orderlines
                where order_id = new.order_id
                  and sku      = new.sku
               )

    -- On the update branch, perform the update
    -- and then return NULL to prevent the 
    -- original insert from occurring
    IF result = 1 THEN
        UPDATE orderlines 
           SET qty = new.qty
         WHERE order_id = new.order_id
           AND sku      = new.sku;
           
        RETURN null;
    END IF;
    
    -- The default branch is to return "NEW" which
    -- causes the original INSERT to go forward
    RETURN new;

END; $BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;

-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
   before insert
   ON ORDERLINES
   FOR EACH ROW
   EXECUTE PROCEDURE orderlines_insert_before_F();

A SQL Server Trigger

SQL Server BEFORE INSERT triggers are significantly different from Postgres triggers. First of all, they operate at the statement level, so that you have a set of new rows instead of just one. Secondly, the trigger must itself contain an explicit INSERT command, or the INSERT never happens. All of this means our SQL Server example is quite a bit more verbose.

The basic logic of the SQL Server example is the same as the Postgres, with two additional complications. First, we must use a CURSOR to loop through the incoming rows. Second, we must explicitly code the INSERT operation for the case where it occurs. But if you can see past the cruft we get for all of that, the SQL Server exmple is doing the same thing:

CREATE TRIGGER upsource_insert_before
ON orderlines
INSTEAD OF insert
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @new_order_id int;
    DECLARE @new_sku      varchar(15);
    DECLARE @new_qty      int;
    DECLARE @result       int;

    DECLARE trig_ins_orderlines CURSOR FOR 
            SELECT * FROM inserted;
    OPEN trig_ins_orderlines;

    FETCH NEXT FROM trig_ins_orderlines
     INTO @new_order_id
         ,@new_sku
         ,@new_qty;

    WHILE @@Fetch_status = 0 
    BEGIN
        -- Find out if there is a row now
        SET @result = (SELECT count(*) from orderlines
                        WHERE order_id = @new_order_id
                          AND sku      = @new_sku
                      )
    
        IF @result = 1 
        BEGIN
            -- Since there is already a row, do an
            -- update
            UPDATE orderlines
               SET qty = @new_qty
             WHERE order_id = @new_order_id
               AND sku      = @new_sku;
        END
        ELSE
        BEGIN
            -- When there is no row, we insert it
            INSERT INTO orderlines 
                  (order_id,sku,qty)
            VALUES
                  (@new_order_id,@new_sku,@new_qty)
            UPDATE orderlines

        -- Pull the next row
        FETCH NEXT FROM trig_ins_orderlines
         INTO @new_order_id
             ,@new_sku
             ,@new_qty;

    END  -- Cursor iteration

    CLOSE trig_ins_orderlines;
    DEALLOCATE trig_ins_orderlines;

END

A Vague Uneasy Feeling

While the examples above are definitely cool and nifty, they ought to leave a certain nagging doubt in many programmers' minds. This doubt comes from the fact that an insert is not necessarily an insert anymore, which can lead to confusion. Just imagine the new programmer who has joined the team an is banging his head on his desk because he cannot figure out why his INSERTS are not working!

We can add a refinement to the process by making the function optional. Here is how we do it.

First, add a column to the ORDERLINES table called _UPSERT that is a char(1). Then modify the trigger so that the UPSERT behavior only occurs if the this column holds 'Y'. It is also extremely import to always set this value back to 'N' or NULL in the trigger, otherwise it will appear as 'Y' on subsequent INSERTS and it won't work properly.

So our new modified explicit upsert requires a SQL statement like this:

INSERT INTO ORDERLINES
       (_upsert,order_id,sku,qty)
VALUES
       ('Y',1234,'ABC',5)

Our trigger code needs only a very slight modification. Here is the Postgres example, the SQL Server example should be very easy to update as well:

   ...trigger declration and definition above
   IF new._upsert = 'Y'
      result = (SELECT.....);
      _upsert = 'N';
   ELSE
      result = 0;
   END IF;
   
   ...rest of trigger is the same

Conclusion

The UPSERT feature gives us simplified code and fewer round trips to the server. Without the UPSERT there are times when the application may have to query the server to find out if a row exists, and then issue either an UPDATE or an INSERT. With the UPSERT, one round trip is eliminated, and the check occurs much more efficiently inside of the server itself.

The downside to UPSERTs is that they can be confusing if some type of explicit control is not put onto them such as the _UPSERT column.

Next week we will see a concept similar to UPSERT used to efficiently create summary tables.

20 comments:

tfor said...

Thanks for the great post. A few times over the past years I've felt like I was cheating by combining an INSERT and UPDATE into a single sproc. I don't feel so bad now seeing that MySQL actually supports it. Unfortunately I use SQL Server 200x so I can't take advantage of that.

One approach I've used before is to explicitly DELETE the row and then perform an INSERT. If the row doesn't exist no exception is raised and the sproc continues with the INSERT regardless.

I'm sure this method must have some performance hits and of course it's useless if the primary key is an identity column.

I would be curious to know what sort of performance hit I'm incurring using this technique. Can you comment?

Kovica said...

I don't know about other database engines, but in DB2 you can do that by using MERGE statement. If a row exists then you can do an update otherwise an insert.

KenDowns said...

@tfor: The UPSERT, as presented in the OP, is the most efficient way to do the operation. All other approaches perform less well. So the performance "hit" is actually a performance boost. This is because it is most efficient to do the check for the existing row in the server itself, inside the trigger. All application-based approaches require more round trips to the server, so it is they who take the hit.

As for identity columns, they are not indicated in all cases, though many programmers use them as if they were. That topic is too large for a comment, but I have several posts on the subject.

Finally, Check out the MySQL link in the OP for their code sample that works with an IDENTITY column.

KenDowns said...

@Kovica, MS SQL Server also supports MERGE as of SQL Server 2008.

tfor said...

@KenDowns, In my case everything takes place in the sproc. E.g. something like:

DELETE FROM MyTable WHERE id = @id
INSERT INTO MyTable (id, c1, c2) VALUES (@id, @v1, @v2)

SQL Server doesn't complain if I attempt to delete a non existent row so if the row already exists the effective behaviour is an update and if it didn't exist the effective behaviour is an insert.

The SQL code for this approach is dead simple so I'm wondering what the downsides are compared to the MERGE method? For small tables with infrequent updates I would suspect this would be a viable approach wouldn't it?

Michael said...

Not to defend MySQL any more than I have to, but they support "INSERT ... ON DUPLICATE KEY UPDATE ..." syntax, which buys you an upsert without triggers.

Also not to bash Postgres any more than I have to, but they should really get around to supporting MERGE one of these days -- it's in SQL2003 (nudge nudge hint hint whine whine...)

antimatroid said...

Your SQL Server example is unnecessarily complex:

update schema.my_table set ... where x = @x and y = @y ... z = @z
if @@rowcount = 0 then
insert into schema.my_table (...) values (...)

KenDowns said...

@tfor: There are two possible issues with the insert/delete method, and the correct solution is context-dependent.

First, if you are deleting from a parent table, you ought to get a constraint violation on the delete.

Second, the Delete/Insert operation is two disk writes, as opposed to a read plus a single write, which is more efficient.

KenDowns said...

@animatroid: nice.

--DD said...

SQLite has the equally useful 'insert or replace' statement (see http://www.sqlite.org/lang_insert.html for details)

El Hombre Gris said...

There is also the non-standard REPLACE in MySQL, that works like an INSERT but deletes the conflicting row before inserting the new one.

http://dev.mysql.com/doc/refman/5.0/es/replace.html

Chad Crawford said...

@antimatroid- your example works if there is only a single row being inserted or if all the rows need to be either inserted or updated. If there are a mix of inserts and updates (which there might be if you are doing an INSERT INTO... SELECT... FROM), you will only update the existing and skip the inserts. There is still a way to avoid the cursor though using an inner and a left join:
UPDATE orderlines
SET qty = inserted.qty
FROM inserted
INNER JOIN orderlines ON orderlines.order_id = inserted.order_id
AND orderlines.sku = inserted.sku;

INSERT INTO orderlines (order_id,sku,qty)
SELECT order_id, sku, qty
FROM inserted
LEFT JOIN orderlines ON orderlines.order_id = inserted.order_id
AND orderlines.sku = inserted.sku;
where orderlines.order_id IS NULL
and orderlines.sku IS NULL

pgeo said...

Not to bash MySQL, but its syntax is at least strange since you have to repeat the values two times. The proof that it's prone to errors is the mistake in the first example in the article:

insert into table (a,c,b) values (1,2,3)
on duplicate key update
b = 2,
c = 3

It will insert value 2 to c and 3 to b, but it will update b to 2 and c to 3....

Patrick Wolf said...

Oracle also supports the MERGE command already for a long time.

Patrick

Robert Treat said...

@Michael The Postgres developers have agreed in principal to support merge, there is even a mapped out syntax and many details of the implementation have been discussed; all that is need now is for some ambitious C coder to show up and start working on a patch. Know anyone? You can find the details on the Postgres TODO list, just scroll down to "Add SQL-standard MERGE/REPLACE/UPSERT command"

Anonymous said...

I do an Update, Delete, Insert. This is necessary because we use staging tables of tens of thousands of rows. So you have to do something like this.

-- Update existing rows
UPDATE Dest
SET Col1 = s.Col1,
Col2 = s.Col2
FROM Dest d INNER JOIN Source s
ON d.id = s.d_id

-- Delete existing rows from the
-- source table
DELETE FROM Source
FROM Source s INNER JOIN Dest d
ON d.id = s.d_id

-- Insert the remaining.
INSERT INTO Dest (Col1, Col2)
SELECT Col1, Col2
FROM Source

KenDowns said...

Anonymous: your comment and approach illustrate a basic reality of databases and programming: rules of thumb are great, but the particulars of the situation have final say.

In the blog I try to provide examples and techniques, but that never means a particular technique is the *only* way, there are always other ways to do it.

Anonymous said...

Wherefore art thou, Ken? We're dying for more content!

KenDowns said...

Anonymous: Thanks for the compliment, this morning I'm doing my first post after about 18 months.

David Underhill said...

Thanks for the PostgreSQL trigger Ken. It is still relevant today (a year and a half later!) since UPSERT is still on the PostgreSQL Todo list.

For those using Python, check out the generic PostgreSQL UPSERT method I wrote. I find it more convenient than writing a trigger like Ken's for every table.