How to insert or update rows in one statement: Databases for Developers #17

Sometimes when adding rows to your database need update-if-exists, insert-if-not-exists logic. Aka an upsert.
You could write this as separate update and insert statements. But there is an easier way:
Merge!
Watch this video for an overview of how merge works.
Further reading:
Merge documentation: docs.oracle.com/en/database/o...
Delete rows not in the source using merge: technology.amis.nl/2006/10/14...
Take the course: devgym.oracle.com/pls/apex/dg...
Need help with SQL? Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
============================
The Magic of SQL with Chris Saxon
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.

[@bestjokesever212]

This is probably the best youtube channel to learn SQL. Hats off to you. Please keep uploading this kind of videos.

[@TheMagicofSQL]

Thanks - you're welcome! Any particular topics you'd like to see covered?

[@bestjokesever212]

@@TheMagicofSQL I would like to see more videos on best practices, pitfalls, common misconceptions, tips and tricks. In general anything that will help a person working with data.

[@TheMagicofSQL]

Thanks for the suggestions - I'll bear these mind
You might be interested in Ask TOM Office Hours. I've got many recordings that go deeper into specific topics - view these at
asktom.oracle.com/pls/apex/f?p=100:570::::570::&cs=3VZVK8QMmqbvOYw3a1sJ6prq9ttSBgOFNRp03H72Gakn3zIK0ia2L36ZTxzapknIEUgCxwM50hH7wlvI14i1ONg

[@vincentenrouelibre]

Thank you so much, this is a life-saver 🙏

[@mohamadrezamotaghi7427]

THAT WAS GREAT, THANKS

[@TheMagicofSQL]

Thanks, glad you liked it :)

[@debpatro]

Does the merge query works effectively while processing large volumes of data?

[@TheMagicofSQL]

It only scans the rows in the source and target tables once. So generally it'll be at least as effective as any other solution
And, as discussed in the video, it's often much faster than a correlated update.

[@TheNanamimijojo]

hi sir i have question i have two daya base have big data want to put data in data base 1 into data base 2 there are 20 table and the two data base have same keys and relation ship

[@TheMagicofSQL]

I'm not sure what it is you're trying to do. Please make a complete example (create tables, inserts) and post it on asktom.oracle.com and we'll see what we can do to help

[@user-gv3oo9hx2m]

will you suggest for single statement for insert and update using oracle ?

[@TheMagicofSQL]

Merge as explained in this video

[@JanaSainik_jsp]

All of sudden performance decreased, we didn't change any code . Even DBA unable to trce the issue. Pls suggest cuases

[@TheMagicofSQL]

There are a huge number of possible reasons; Jonathan Lewis has catalogued many at
jonathanlewis.wordpress.com/2013/12/23/plan-changes/

[@rohitkumar-nk6sd]

Hi iam getting error when iam running in postgresql what to do help me out

[@TheMagicofSQL]

I specialize in Oracle Database; if you need help with PostgreSQL you'll need to find another forum.
Note PostgreSQL doesn't support MERGE yet; if you're trying to do this you'll need to use another method to upsert the data.

[@Pluto102]

Can you make few videos on MATCH RECOGNIZE

[@TheMagicofSQL]

Patience! It's on my to-do list; it'll be a while before I can produce one

[@dn9416]

Nice

[@jagadishs3931]

He likes to throw things so much