Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 295

UPDATE O 

  SET shipcountry = C.country, 

      shipregion = C.region, 

      shipcity = C.city 

FROM dbo.Orders AS O 

  JOIN dbo.Customers AS C 

    ON O.custid = C.custid 

WHERE C.country = 'UK';

Another solution to this exercise uses CTEs. You can define a CTE based on a SELECT query that 

joins dbo.Orders and dbo.Customers and returns both the target location attributes from dbo.Orders 

and the source location attributes from dbo.Customers. The outer query would then be an UPDATE 

statement modifying the target attributes with the values of the source attributes. Here’s the com-

plete solution statement.

WITH CTE_UPD AS 

( 

  SELECT 

    O.shipcountry AS ocountry, C.country AS ccountry, 

    O.shipregion  AS oregion,  C.region  AS cregion, 

    O.shipcity    AS ocity,    C.city    AS ccity 

  FROM dbo.Orders AS O 

    JOIN dbo.Customers AS C 

      ON O.custid = C.custid 

  WHERE C.country = 'UK' 

) 

UPDATE CTE_UPD 

  SET ocountry = ccountry, oregion = cregion, ocity = ccity;

You can also use the MERGE statement to achieve this task. As explained earlier, even though in a 

MERGE statement you usually want to specify both the WHEN MATCHED and WHEN NOT MATCHED 

clauses, the statement supports specifying only one of the clauses. Using only a WHEN MATCHED 

clause with an UPDATE action, you can write a solution that is logically equivalent to the last two solu-

tions. Here’s the complete solution statement.

MERGE INTO dbo.Orders AS O 

USING dbo.Customers AS C 

   ON O.custid = C.custid 

   AND C.country = 'UK' 

WHEN MATCHED THEN 

  UPDATE SET shipcountry = C.country, 

             shipregion = C.region, 

             shipcity = C.city;

www.it-ebooks.info

---

www.it-ebooks.info

---