How to understand T-SQL in the merge statement (ii)

How to understand the merge statement in T-SQL (II)

Written in front of the words: How to understand T-SQL in the merge statement, basically the merge statement to talk about, in the back of the article, thrown a few knots, at that time did not want to understand how to use the text expression, this article to answer a few knot, but also a "unrestrained" text, Let's see.

= = = Text Start = =

First Look at table one (student_target) and Table II (Student_source) below.

First, when matched part　　

Execute the following SQL statement:

MERGE into Student_target as stusing Student_source as SsON St. Sno = ss. Sno when    matched then         UPDATE SET St. Sname = ss. Sname;

After executing the above SQL statement, the contents of the current Student_target table should also be readily available, such as:

The sname value of the sno=1,2 row in the Student_target table is update by the corresponding value in the Student_source, but how did this result come about? Today, using another way of thinking to understand how to get, we add an intermediate process, expressed as follows:

(1) The SQL statement above: MERGE into ... When matched, can be likened to a INNER JOIN statement: SELECT * from Student_target as St INNER join Student_source as SS on St. Sno=ss. Sno, after an internal join, the results are as follows:

Where the red box is the student_target part, the blue is the student_source part, what does this figure represent? We can assume that the actions taken later only affect the red student_target part of the graph, so then update set ST.SNAME=SS.SNAME is executed, so the original table one student_target only The part of the red box is affected, that is, the red box is sname in the blue box sname to update, the other rows (3, ' CC ') are not affected, so the final result of the Student_target is:

(2) According to this idea, if then update set St.sname=ss.sname changed to then delete, naturally only the red box part is deleted, and finally student_target the result, just leave a line.

(3) According to this idea, what happens if then update set St.sname=ss.sname changes to then insert values (ss.sno,ss.sname)? look at the front of the red and blue parts, the red part of the content is not NULL, so you can not use the blue part of the right to insert into, so should be an error, execute, did report the following errors:

　　　

Now it's time to understand when matched is not allowed to have INSERT statements, then when to allow INSERT statements, then look down.

Second, when is not the matched by target part　　

The front when matched we understand through the inner join idea, this time not matched by target we use right outer join idea to think about, the same original table is table one and table two, again map the following:

　

Execute the following SQL statement:

MERGE into Student_target as stusing Student_source as SsON St. Sno = ss. Sno when not    matched by TARGET then        INSERT VALUES (ss. Sno,ss. Sname);

Do not worry about the above running results first, we first use right outer join idea to comb a bit:

(1) The SQL statement above: MERGE into ... When the not matched by TARGET can be likened to a right outer JOIN statement: SELECT * from student_target as St right outer join Student_source as SS on St. Sno=ss. Sno, after the right outer join (you should be very clear about the outer join concept), the results are as follows:

Where the red box is the student_target part, the blue is the student_source part, what does this figure represent? We can assume that the actions taken later only affect the student_target portion of the red in the graph, so then insert VALUES (ss) is executed. Sno,ss. Sname), then the original table one student_target only the portion of the red box is affected, that is, the red box sno and Sname are null in turn by the blue Box Sno and Sname to insert, where you can insert, Because there are null values in the third and fourth rows, the result of the last Student_target is:

(2) According to this idea, if then insert values (ss. Sno,ss. Sname) change to then delete, naturally only the red box part is deleted, but the red part of the third row and the fourth row is all null values, all null natural can't delete ah, so should be error, run a bit, error is as follows:

(3) According to this idea, if then insert values (ss. Sno,ss. Sname) change to then update set St. Sname=ss. Sname, also because full null line error, because all null can not update Ah, error message is as follows:

　　　

It is now time to understand why not matched by Target does not allow the update and DELETE statements.

Third, when is not the matched by source part　　

The front when matched we through the inner join idea to understand, when not matched by target with right outer join idea to understand, this time not matched by source naturally should use Left outer join to understand, the specific understanding of the process in the second part, it is not detailed written, directly on the results:

The same original table is also table I and table two, the map is as follows:

(1) Execute the following SQL statement: SELECT * from Student_target as St left outer joins Student_source as SS on St. Sno=ss. Sno, after the left outer join, the result is as follows:

　

(2) Execute the following SQL statement:

MERGE into Student_target as stusing Student_source as SsON St. Sno = ss. Sno when not    matched by SOURCE then        Delete;

The result of the last Student_target operation is:

Deleted rows in Student_target that are not in the Student_source.

(2) Execution of INSERT and UPDATE statements also error.

Iv. Final Summary

I admire you can see here, finally a summary of it:

(1) When matched: analogy inner join to think, you can perform update and delete operations, unable to perform the insert operation.

(2) When isn't matched by target: analogy right outer join to think that you can perform an insert operation and cannot perform update and delete operations. (not in the target table (not matched), but in the source table)

(3) When the matched by source, analogy to the left outer join to think, you can perform the delete operation, unable to perform the insert and update operation. (there is in the target table, but not in the source table (not matched))

Note: The code used during the practice:

Use Testdbgomerge to Student_target as stusing Student_source as SsON St. Sno = ss. Sno    --when matched         --then UPDATE SET St. Sname = ss. Sname        --then  INSERT VALUES (ss.sno,ss.sname)    --when not matched by Target--then UPDATE set st.sname= Ss.sname    --when not matched by SOURCE--then UPDATE set St. Sname=ss. Sname,st. Sno=ss. Sno; TRUNCATE TABLE dbo. Student_targettruncate TABLE dbo. Student_sourceinsert into Student_target (sno,sname) VALUES (1, ' AA '), (2, ' BB '), (3, ' CC ') inserts into Student_source (SNO, sname) VALUES (1, ' xiaoming '), (2, ' Xiaoli '), (4, ' Xiaohong '), (5, ' Xiaoping ') SELECT * FROM dbo. Student_targetselect * FROM dbo. Student_sourceselect * FROM dbo. Student_target as St INNER JOIN dbo. Student_source as SsON St. Sno=ss. Snoselect * FROM dbo. Student_target as St right OUTER JOIN dbo. Student_source as SsON St. Sno=ss. Snoselect * FROM dbo. Student_target as St left OUTER JOIN dbo. Student_source as SsON St. Sno=ss. Sno

How to understand the merge statement in T-SQL (II)