Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
Phần đầu của câu truy vấn kết hợp xác định các bảng mục tiêu và nguồn và cách chúng liên quan. Sau khi định nghĩa bảng, có một điều khoản bắt buộc đối với mỗi sự kết hợp phù hợp, như trong cú pháp này đơn giản: | Part II Manipulating Data With Select The first section of the merge query identifies the target and source tables and how they relate. Following the table definition there s an optional clause for each match combination as shown in this simplified syntax MERGE TargetTable USING SourceTable ON join conditions WHEN Matched THEN DML WHEN NOT MATCHED BY TARGET THEN DML WHEN NOT MATCHED BY SOURCE THEN DML Applying the MERGE command to the airline check-in scenario there s an appropriate action for each match combination If the row is in both FlightPassengers the target and CheckIn the source then the target is updated with the CheckIn table s seat column. If the row is present in CheckIn the source but there s no match in FlightPassenger the target then the row from CheckIn is inserted into FlightPassenger. Note that the data from the source table is gathered by the INSERT command using INSERT. .VALUES. If the row is present in FlightPassenger the target but there s no match in CheckIn the source then the row is deleted from FlightPassenger. Note that the DELETE command deletes from the target and does not require a WHERE clause because the rows are filtered by the MERGE command. Here s the complete working MERGE command for the scenario MERGE FlightPassengers F USING CheckIn C ON C.LastName F.LastName AND C.FirstName F.FirstName AND C.FlightCode F.FlightCode AND C.FlightDate F.FlightDate WHEN Matched THEN UPDATE SET F.Seat C.Seat WHEN NOT MATCHED BY TARGET THEN INSERT FirstName LastName FlightCode FlightDate Seat VALUES FirstName LastName FlightCode FlightDate Seat WHEN NOT MATCHED BY SOURCE THEN DELETE The next query looks at the results of the MERGE command returning the finalized passenger list for SQL Server Airlines flight 2008 SELECT FlightID FirstName LastName FlightCode FlightDate Seat FROM FlightPassengers 372 www.getcoolebook.com Modifying Data 15 Result FlightID FirstName LastName FlightCode FlightDate Seat 1 Paul Nielsen SS2008 2009-03-01 9F 2 Sue Jenkins
