UPSERTs are tempting, with perks like single round trip to DB, atomic properties, and simpler SQL client code.
Then, if your update depends on the existing row data, there's even Conditional UPSERT.
A conditional UPSERT can have one of three outcomes:
- Row is inserted as a new row
- An existing row/column is updated (condition evaluated True).
- Existing row/column wasn't updated (condition evaluated False).
For non-conditional UPSERTs the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.
For conditional UPSERTs I saw non consistent results. affected rows was 2 OR 3, when an existing row was updated (condition true), 2 when an existing row wasn't updated. 1 for new row is inserted.
Such inconsistent behavior could easily cause bugs. I didn't find a clear pattern of when it happens. I also didn't find proper documentation of what should is the expected affected-row value.
Since the ON DUPLICATED KEY UPDATE uses an IF statement I suspect it loses track of the results of the IF statement. Also each column's condition can evaluated differently than others columns conditions.
Conclusion
without knowing what to expect of the # of affected rows, and UPSERTs being easily breakable in nature when switching columns update order). I decided to ditch them completely, and ended up implementing the conditional logic in Java client side, using more than one SQL command wrapped in a transaction.My conditional UPSERT (expect inconsistent affected rows value):
INSERT INTO account_last_touch(service_id, account_name, user_name, touch_time)
VALUES('123', '456', 'u1', '2016-07-19 12:11:15')
ON DUPLICATE KEY UPDATE
user_name =IF('2016-07-23 12:11:15'>touch_time, 'u1', user_name),
touch_time = IF('2016-07-24 12:11:15'>touch_time, '2016-07-24 12:11:15', touch_time)
ReplyDeleteThank you for providing such a useful information. your post so timely for me .Please visit
spotting errors
such a good post shating with us..thank you.
ReplyDeleteKIITEE Admit Card 2017
TS EAMCET Application form 2017
LPU NEST Admit Card 2017
AFCAT Answer Key 2017
CG Vyapam Food Inspector Answer Key 2017
RBI Assistant Mains Result 2017
KSWC JE Accountant Result 2017
Make your Relationship long lasting
such a wonderful details sharing with us...thanks..
ReplyDeleteJEE Main Answer Key 2017
JIPMER MBBS application form 2017
TS PGECET Admit Card 2017
TANCET Result 2017
MHT CET Admit Card 2017
OFB Trade Apprentice Answer Key 2017
A wonderful post sharing with us..thank you...
ReplyDeleteTMB Clerk Result 2017
JKSSB Objective Type Exam Answer Key 2017
SSC MTS Admit Card 2017
UPSC NDA NA Admit Card 2017
Telangana Staff Nurse Answer Key 2017
NEET Admit card 2017
Best English Classes in Bangalore
ReplyDeleteNice post Thank you for sharing and it's helpful to the people.
ReplyDeleteBest Courses in Digital Marketing
This comment has been removed by the author.
ReplyDeleteThank you a lot for providing individuals with a very
ReplyDeletespectacular possibility to read critical reviews from this site.
aws training in bangalore
aws training in chennai