Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 291

5

Update all orders in the dbo.Orders table that were placed by United Kingdom customers and set 

their shipcountry, shipregion, and shipcity values to the country, region, and city values of the corre-

sponding customers.

6

When you’re done, run the following code for cleanup.

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders; 

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;

Solutions

This section provides solutions to the preceding exercises.

1-1

Make sure that you are connected to the TSQL2012 database.

USE TSQL2012;

Use the following INSERT VALUES statement to insert a row into the Customers table with the 

values provided in the exercise.

INSERT INTO dbo.Customers(custid, companyname, country, region, city) 

  VALUES(100, N'Coho Winery', N'USA', N'WA', N'Redmond');

1-2

One way to identify customers who placed orders is to use the EXISTS predicate, as the following 

query shows.

  SELECT custid, companyname, country, region, city 

  FROM Sales.Customers AS C 

  WHERE EXISTS 

    (SELECT * FROM Sales.Orders AS O 

     WHERE O.custid = C.custid);

To insert the rows returned from this query into the dbo.Customers table, you can use an INSERT 

SELECT statement as follows.

INSERT INTO dbo.Customers(custid, companyname, country, region, city) 

  SELECT custid, companyname, country, region, city 

  FROM Sales.Customers AS C 

  WHERE EXISTS 

    (SELECT * FROM Sales.Orders AS O 

     WHERE O.custid = C.custid);

www.it-ebooks.info

---