Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 269

UPDATE dbo.T1 

 

  SET (col1, col2, col3) = 

 

      (SELECT col1, col2, col3 

       FROM dbo.T2 

       WHERE T2.keycol = T1.keycol) 

        

WHERE EXISTS 

  (SELECT * 

   FROM dbo.T2 

   WHERE T2.keycol = T1.keycol 

     AND T2.col4 = 'ABC');

But as you can see, this version would still be more complicated than the join version, because 

it requires separate subqueries for the filtering part and for obtaining the attributes from the other 

table for the assignments.

assignment 

UPDATE

T-SQL supports a proprietary UPDATE syntax that both updates data in a table and assigns values to 

variables at the same time. This syntax saves you the need to use separate UPDATE and SELECT state-

ments to achieve the same task.

One of the common cases for which you can use this syntax is in maintaining a custom 

sequence/autonumbering mechanism when the identity column property and the sequence object 

don’t work for you. One example where this might be the case is if you need a sequencing mechanism 

that guar antees no gaps. The idea is to keep the last-used value in a table, and to use this special UP-

DATE syntax to increment the value in the table and assign the new value to a variable.

Run the following code to first create the Sequence table with the column val, and then populate it 

with a single row with the value 0—one less than the first value that you want to use.

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

 

CREATE TABLE dbo.Sequences 

( 

  id VARCHAR(10) NOT NULL 

    CONSTRAINT PK_Sequences PRIMARY KEY(id), 

  val INT NOT NULL 

); 

INSERT INTO dbo.Sequences VALUES('SEQ1', 0);

Now, whenever you need to obtain a new sequence value, use the following code.

DECLARE @nextval AS INT; 

 

UPDATE dbo.Sequences 

  SET @nextval = val += 1 

WHERE id = 'SEQ1'; 

 

SELECT @nextval;

www.it-ebooks.info

---

270  

Microsoft SQL Server 2012 T-SQL Fundamentals

The code declares a local variable called @nextval. Then it uses the special UPDATE syntax to incre-

ment the column value by 1, assigns the updated column value to the variable, and presents the value 

in the variable. The assignments in the SET clause take place from right to left. That is, first val is set to 

val + 1, then the result (val + 1) is set to the variable @nextval.

The specialized UPDATE syntax is run as an atomic operation, and it is more efficient than using 

separate UPDATE and SELECT statements because it accesses the data only once. 

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

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

Merging Data

SQL Server 2008 and SQL Server 2012 support a statement called MERGE that allows you to modify 

data, applying different actions (INSERT, UPDATE, and DELETE) based on conditional logic. The 

MERGE statement is part of the SQL standard, although the T-SQL version adds a few nonstandard 

extensions to the statement. 

A task achieved by a single MERGE statement will typically translate to a combination of several 

other DML statements (INSERT, UPDATE, and DELETE) without MERGE. The benefit of using MERGE 

over the alternatives is that it allows you to express the request with less code and run it more ef-

ficiently because it requires fewer accesses to the tables involved.

To demonstrate the MERGE statement, I’ll use tables called dbo.Customers and dbo.Customers-

Stage. Run the code in Listing 8-2 to create those tables and populate them with sample data.

LISTING 8-2

  Code That Creates and Populates Customers and CustomersStage

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

GO 

 

CREATE TABLE dbo.Customers 

( 

  custid      INT         NOT NULL, 

  companyname VARCHAR(25) NOT NULL, 

  phone       VARCHAR(20) NOT NULL, 

  address     VARCHAR(50) NOT NULL, 

  CONSTRAINT PK_Customers PRIMARY KEY(custid) 

);

www.it-ebooks.info

---