Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 279

TOP option based on a logical ORDER BY clause that defines precedence among rows. You can then 

issue the modification statement against the table expression.

For example, the following code deletes the 50 orders with the lowest order ID values rather than 

just any 50 rows.

WITH C AS 

( 

  SELECT TOP(50) * 

  FROM dbo.Orders 

  ORDER BY orderid 

) 

DELETE FROM C;

Similarly, the following code updates the 50 orders with the highest order ID values, increasing 

their freight values by 10.

WITH C AS 

( 

  SELECT TOP(50) * 

  FROM dbo.Orders 

  ORDER BY orderid DESC 

) 

UPDATE C 

  SET freight += 10.00;

In SQL Server 2012, you can use the OFFSET-FETCH option instead of TOP in the inner SELECT 

queries. Here’s the revised DELETE example.

WITH C AS 

( 

  SELECT * 

  FROM dbo.Orders 

  ORDER BY orderid 

  OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY 

) 

DELETE FROM C;

And here’s the revised UPDATE example.

WITH C AS 

( 

  SELECT * 

  FROM dbo.Orders 

  ORDER BY orderid DESC 

  OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY 

) 

UPDATE C 

  SET freight += 10.00;

www.it-ebooks.info

---

280  

Microsoft SQL Server 2012 T-SQL Fundamentals