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.
47 comments:
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?
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.
@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.
@Kovica, MS SQL Server also supports MERGE as of SQL Server 2008.
@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?
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...)
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 (...)
@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.
@animatroid: nice.
SQLite has the equally useful 'insert or replace' statement (see http://www.sqlite.org/lang_insert.html for details)
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
@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
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....
Oracle also supports the MERGE command already for a long time.
Patrick
@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"
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
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.
Wherefore art thou, Ken? We're dying for more content!
Anonymous: Thanks for the compliment, this morning I'm doing my first post after about 18 months.
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.
Most ideal approach to Solve MySQL Max User Connection Error through MySQL Technical Support
The MySQL database is both open source and simple to utilize yet the vast majority of the clients confronting issue when they execute embed as well as refresh of a large number of columns consistently and around then they need to confront this specialized hiccups. Well! We encourage you to tackle this issue through MySQL Remote Support or MySQL Remote Service. We give the help which flawlessly meets the specialized and operational administration desires. So rapidly take our help and investigate the best help with our specialists.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Thank you so much for providing information about SSIS Upsert and its uses.
SSIS upsert
at SynergisticIT offer the best 5 minute aws test
I have read so many content regarding the blogger lovers but this piece of writing is in fact a nice post, keep it up.
The web address for redeeming an office product key and downloading, installing, and activating an office product setup is Office.com/setup.
We've already written a few posts on how to fix these problems and easily install Office on your PC/Mac by simply clicking the below links:
www.office.com/setup
office.com/setup
office.com/setup
office.com/setup login
There are products for students, professionals, home users as well as business and these can be easily installed/deployed with the help of the Office product key. Once you decide to purchase the Office product suite of your choice, it becomes easier for you to set up the product on your device/s. So, let’s walk you through the complete step-by-step procedure to set up Office.
office.com/setup
www.office.com/setup
Microsoft 365 is a collaboration cloud that allows you to follow your passion while still running your company. Microsoft 365 is more than just Word, Excel, and PowerPoint; it combines best-in-class productivity applications with powerful cloud services, mobile management, and enhanced security in one seamless experience.
Get Microsoft365 Home & Student installed through below links:
office.com/setup home and student 2019
office.com/setup home and business 2019
How to Get Microsoft Office works for Windows?
Click Below Links:
office.com/setup
www.office.com/setup
buy microsoft365
msofficeworks
microsoft365.com
Hey Everyone,Are you getting errors when you try to activate your Microsoft office activation subscription?
Then read on to get step by step guide to solve Microsoft office activation errors. Protect your windows, Mac, and Android devices. Get and easily run Anti Viruses and Learn how to download, install, and activate your Office setup on your computer and other devices by the easiest steps. Just visit our website and Troubleshoot the most commonly occuring errors for Microsoft office365 by the easiest steps. Install, and activate your Office setup on your computer and other devices.
office.com/setup
www.office.com/setup
dqqw qw
qwdwq qwd wqdqw
wqdqw
onlyfans premium account generator
Funny shirt
Funny Shirt quotes
Open the browser and type in Spectrum router’s IP address in the address bar. You can find it at the back of the router. A new web page will appear for spectrum router login. When you log in the first time, you can do so by using the standard username and password that it’s set to. Once you’ve logged in, however, you can change these settings to make the password harder and more secure.
바카라사이트
Your web site is great. I was extremely touched by this post. I'm so pleased to locate the write-up I have actually been seeking for a long period of time. I covet the amount of individuals sympathize with your writing. You discover a great deal of expertise from my writing.
Good information.
바카라사이트
파워볼사이트
카지노사이트
파워볼사이트 As I website possessor I believe the articles here is very excellent, thanks for your efforts.
Vielen Dank für den Austausch, das ist ein fantastischer Blog-Beitrag, nochmals vielen Dank. Wirklich großartig.
What’s up, everything is going nicely in this blog here, keep up writing.
-ag
Excellent and nice post. It will beneficial for everyone.
-ag
Thanks for sharing such a wonderful post.
-ag
Wow! Such an amazing and helpful post this is.-ag
إذا كنت تبحث عن أفضل أسعار كشف تسربات المياه بالرياض وسائر أنحاء المملكة، فنحن هنا لنقدم لك أفضل ما لدينا من خدمات وبأفضل الأسعار التنافسية لعام 2025، حيث تعد شركة تسليك المملكة واحدة من كبرى المؤسسات التي لها باع طويل في هذا المجال، والتي تعتمد على أحدث الوسائل والتقنيات للكشف عن تسربات المياه بمختلف أنواعها، بما في ذلك التسربات الخفية وتلك العميقة.
شركة صيانة مكيفات بالرياض خصم 30% أصبحت المكيفات من أكثر الأجهزة المنزلية المستخدمة فى الفترة الأخيرة حيث زاد الإقبال عليها بسبب إرتفاع درجات الحرارة بالأخص في المملكة العربية السعودية.
Wow, that’s what I was exploring for, what a stuff!
existing here at this weblog, thanks admin of this web page.
you have some really great posts and I feel I would be a good asset. thank you
Thanks for such a valuable post.
I am waiting for your next post.
Post a Comment