Oracle uses the merge into statement to update data

The beautiful and capable virtuous daughter-in-law in SQL statements--merge into(2010-02-02 17:09:45)

tags:   Merge  into   Pk  matched   Then  update   And  associating   Same name field   update condition   Multi-table   it

Category:  it

In the daily operation, the merge into this SQL statement is very useful. The following is a brief introduction:
Merge into Bd_defdoc D--table to be modified

using (
Select L.pk_defdoclist,l.doclistcode from bd_defdoclist L--association/Basis table
) TT
On d.pk_defdoclist = tt.pk_defdoclist--Updated condition
When matched then update set d.doclistcode = Tt.doclistcode-matches the associated condition for update processing

When isn't matched then--does not match the association condition, makes the insertion processing. If you are updating only, the following statement can be omitted.
Insert VALUES (Tt.pk_defdoclist,tt ...);

--The association table can be associated with multiple tables, such as:

Example 1:

Merge into aa_001 a using (
Select S.pk_defdoclist,l.doclistcode,s.pk_defdoc,s.doccode,s.docname from Bd_defdoc s
INNER JOIN bd_defdoclist L on S.pk_defdoclist=l.pk_defdoclist
) TT
On a.doclistcode = Tt.doclistcode and A.doccode=tt.doccode
When matched then update set a.pk_defdoc = Tt.pk_defdoc

--If you have a name for the field in the associated table, you must add the table name if you want to view it. If you only use a field with the same name as one of the tables, you can mask the field with the same name in the other table, and see the comments section in the following SQL. For example:

Example 2:

Merge into Bd_defdoc D using (
Select P.pk_defdoc as P_pk_defdoc,

--p.doclistcode as p_doclistcode,p.doccode as p_doccode,p.docname as P_docname,

S.doclistcode,s.doccode,s.docname, S.pdoccode
From aa_001 s inner join aa_001 p on S.pdoclistcode=p.doclistcode and S.pdoccode=p.doccode
) TT
On d.doclistcode = Tt.doclistcode and D.doccode=tt.doccode
When matched then update set d.pk_defdoc1 = Tt.p_pk_defdoc

--If you add conditions to the table you want to modify, you can use the following syntax:

Example 3:

Merge into Bd_psndoc p using (select Pk_om_job,jobseries from Om_job) O on P.pk_om_job=o.pk_om_job
When matched and p.jobseries<>o.jobseries

Then update set p.jobseries=o.jobseries

Example 4:

Merge into Bd_psndoc p using (select Pk_om_job,jobseries from Om_job) O on P.pk_om_job=o.pk_om_job
When matched and (p.jobseries = ' 0001b710000000000qyn ' or p.jobseries= ' 0001b710000000000qyq ')
Then update set p.jobseries=o.jobseries

In the ointment is: Merge into as if in Oracle, DB2 can be able to "on the hall, got the kitchen, write code, check out the exception", but in SQL Server but a little "still hold pipa half cover, awaited not come out" flavor! Interested netizens quickly find a merge into to try it!

Oracle uses the merge into statement to update data