Examples of Oracle Merge into

Content from Oracle 11g practical Tutorials Zheng Achi, etc.

In Oracle 11g, there is a merge statement that performs an INSERT, update, or delete operation on the target table based on the results of the connection to the original table. For example, by inserting, updating, or deleting rows in another table based on the differences found in one table, this method can synchronize information for two tables. The syntax format is as follows

Merge into < target table name >

Using < original table name >on < conditional expressions >

When matched and then {update Set ... | Delete

When isn't matched then insert (...) VALUES ()

Merge into a
Using XSB on (a.xh=xsb. Number)
When matched then update set a.xm = XSB. Name, A.XB=XSB. Gender, A.CSSJ = xsb. Time of birth, A.zy=xsb. Major, A.zxf=xsb. Total credits, A.BZ=XSB. Notes
When isn't matched then insert values (XSB, XSB. Name, xsb. Sex, xsb. Birth time, Xsb. Major, Xsb. Total credits, XSB. Notes)

Database

 Create TableA (XHChar(6) not NULL Primary Key, XMChar(8) not NULL, XBChar(2) not NULL, CSSJ date not NULL, ZYChar( -)NULL, ZXF Number(2)NULL, BZvarchar( $)NULL );

----------------------------------------------------------file created-Sunday-July -24-2016------------------------------------------------------------------------------------------------------------------DDL for Table XSB--------------------------------------------------------  CREATE TABLE" SCOTT". " XSB "(" Study No. "CHAR(6BYTE), "name"CHAR(8BYTE), "gender"CHAR(2BYTE)DEFAULT 'male', "date of birth", "professional"CHAR( ABYTE), "Total Credits" Number(2,0), "Remarks"VARCHAR2( $BYTE)) SEGMENT CREATION IMMEDIATE PCTFREETenPctused +Initrans1Maxtrans255nocompress LOGGING STORAGE (INITIAL65536 NEXT 1048576Minextents1MAXEXTENTS2147483645Pctincrease0Freelists1FREELIST GROUPS1Buffer_poolDEFAULTFlash_cacheDEFAULTCell_flash_cacheDEFAULT) tablespace "USERS"; REM INSERTING intoSCOTT. XSBSETDEFINEOFF;Insert  intoSCOTT. XSB (School number, name, gender, time of birth, major, total credits, notes)Values('151114','Zhou Ho Jun','male', To_date ('2 May-September on -98','DD-MON-RR'),'Computer', -,NULL);Insert  intoSCOTT. XSB (School number, name, gender, time of birth, major, total credits, notes)Values('151101','Wang Lin','male', To_date ('February-October on -97','DD-MON-RR'),'Computer', -,NULL);Insert  intoSCOTT. XSB (School number, name, gender, time of birth, major, total credits, notes)Values('151103','Wang','female', To_date ('October-June on -96','DD-MON-RR'),'Computer', -,NULL);Insert  intoSCOTT. XSB (School number, name, gender, time of birth, major, total credits, notes)Values('151202','Wang Lin','male', To_date ('2 September-January on -96','DD-MON-RR'),'Communication Engineering', +,'One of the classes failed, to be retake');Insert  intoSCOTT. XSB (School number, name, gender, time of birth, major, total credits, notes)Values('151108','Lin Yi-Fan','male', To_date ('August-May on -96','DD-MON-RR'),'Computer', the,'have completed a course in advance');Insert  intoSCOTT. XSB (School number, name, gender, time of birth, major, total credits, notes)Values('151204','Ma Linlin','female', To_date ('February-October on -96','DD-MON-RR'),'Communication Engineering', the,NULL);----------------------------------------------------------DDL for Index sys_c0011202--------------------------------------------------------  CREATE UNIQUE INDEX"SCOTT". " sys_c0011202 " on" SCOTT". " XSB "(" study number ") PCTFREETenInitrans2Maxtrans255 COMPUTE STATISTICSSTORAGE (INITIAL65536 NEXT 1048576Minextents1MAXEXTENTS2147483645Pctincrease0Freelists1FREELIST GROUPS1Buffer_poolDEFAULTFlash_cacheDEFAULTCell_flash_cacheDEFAULT) tablespace "USERS";----------------------------------------------------------Constraints for Table XSB--------------------------------------------------------  ALTER TABLE"SCOTT". " XSB "MODIFY" ("School Number") not NULLENABLE); ALTER TABLE"SCOTT". " XSB "MODIFY" ("Name" not NULLENABLE); ALTER TABLE"SCOTT". " XSB "MODIFY" ("Gender" not NULLENABLE); ALTER TABLE"SCOTT". " XSB "MODIFY" ("Birth Time") not NULLENABLE); ALTER TABLE"SCOTT". " XSB "ADD PRIMARY KEY("study number") USINGINDEXPCTFREETenInitrans2Maxtrans255 COMPUTE STATISTICSSTORAGE (INITIAL65536 NEXT 1048576Minextents1MAXEXTENTS2147483645Pctincrease0Freelists1FREELIST GROUPS1Buffer_poolDEFAULTFlash_cacheDEFAULTCell_flash_cacheDEFAULT) tablespace "USERS" ENABLE;

Examples of Oracle Merge into