Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 293

2

To delete orders placed before August 2006, you need a DELETE statement with a filter based on the 

predicate orderdate < ‘20060801’. As requested, use the OUTPUT clause to return attributes from the 

deleted rows.

DELETE FROM dbo.Orders 

  OUTPUT deleted.orderid, deleted.orderdate 

WHERE orderdate < '20060801';

3

This exercise requires you to write a DELETE statement that deletes rows from one table (dbo.Orders) 

based on the existence of a matching row in another table (dbo.Customers). One way to solve the 

problem is to use a standard DELETE statement with an EXISTS predicate in the WHERE clause, like 

this.

DELETE FROM dbo.Orders 

WHERE EXISTS 

  (SELECT * 

   FROM dbo.Customers AS C 

   WHERE Orders.custid = C.custid 

     AND C.country = N'Brazil');

This DELETE statement deletes the rows from the dbo.Orders table for which a related row exists 

in the dbo.Customers table with the same customer ID as the order’s customer ID and the customer’s 

country is Brazil.

Another way to solve this problem is to use the T-SQL–specific DELETE syntax based on a join, like 

this.

DELETE FROM O 

FROM dbo.Orders AS O 

  JOIN dbo.Customers AS C 

    ON O.custid = C.custid 

WHERE country = N'Brazil';

Note that there are no matched rows, of course, if the previous DELETE is executed. 

The join between the dbo.Orders and dbo.Customers tables serves a filtering purpose. The join 

matches each order with the customer who placed the order. The WHERE clause filters only rows for 

which the customer’s country is Brazil. The DELETE FROM clause refers to the alias O representing the 

table Orders, indicating that Orders is the target of the DELETE operation.

www.it-ebooks.info

---

294  

Microsoft SQL Server 2012 T-SQL Fundamentals

As a standard alternative, you can use the MERGE statement to solve this problem. Even though 

you would normally think of using MERGE when you need to apply different actions based on condi-

tional logic, you can also use it when you need to apply one action when a certain predicate is TRUE. 

In other words, you can use the MERGE statement with the WHEN MATCHED clause alone; you don’t 

have to have a WHEN NOT MATCHED clause as well. The following MERGE statement handles the 

request in the exercise.

MERGE INTO dbo.Orders AS O 

USING dbo.Customers AS C 

  ON O.custid = C.custid 

  AND country = N'Brazil' 

WHEN MATCHED THEN DELETE;

Again, note that there are no matched rows if either of the previous DELETE statements is executed. 

This MERGE statement defines the dbo.Orders table as the target and the dbo.Customers table 

as the source. An order is deleted from the target (dbo.Orders) when a matching row is found in the 

source (dbo.Customers) with the same customer ID and the country Brazil. 

4

This exercise involves writing an UPDATE statement that filters only rows for which the region at-

tribute is NULL. Make sure you use the IS NULL predicate and not an equality operator when looking 

for NULL marks. Use the OUTPUT clause to return the requested information. Here’s the complete 

UPDATE statement.

UPDATE dbo.Customers 

  SET region = '' 

OUTPUT 

  deleted.custid, 

  deleted.region AS oldregion, 

  inserted.region AS newregion 

WHERE region IS NULL;

5

One way to solve this exercise is to use the T-SQL–specific UPDATE syntax based on a join. You can 

join dbo.Orders and dbo.Customers based on a match between the order’s customer ID and the cus-

tomer’s customer ID. In the WHERE clause, you can filter only the rows where the customer’s country 

is the United Kingdom. In the UPDATE clause, specify the alias you assigned to the dbo.Orders table 

to indicate that it’s the target of the modification. In the SET clause, assign the values of the shipping 

location attributes of the order to the location attributes of the corresponding customer. Here’s the 

complete UPDATE statement.

www.it-ebooks.info

---