Updating multiple rows without loops

Question

I want to update several lines at once in the update statement without using a loop.

I have the following table with some entries as shown below:

Table:

create table test ( col1 int, col2 int, col3 varchar(20), col4 datetime, name varchar(max) ); 

Insert:

 insert into test values(111,999,'A101','2014-01-01',''); insert into test values(112,998,'A102','2014-01-02',''); insert into test values(113,997,'A103','2014-01-03',''); insert into test values(114,996,'A104','2014-01-04',''); insert into test values(111,999,'A101','2014-01-01',''); insert into test values(114,996,'A104','2014-01-04',''); insert into test values(115,995,'A105','2014-01-05',''); insert into test values(116,994,'A106','2014-01-06',''); 

Now I want to update the name for specific dates, for example, as shown below:

Update name D1 for a date between 2014-01-01 and 2014-01-02
Update name D2 for a date between 2014-01-04 and 2014-01-06

Expected Results Table :

 col1 col2 col3 col4 name ------------------------------------------------------- 111 999 A101 2014-01-01 00:00:00.000 D1 112 998 A102 2014-01-02 00:00:00.000 D1 113 997 A103 2014-01-03 00:00:00.000 114 996 A104 2014-01-04 00:00:00.000 D2 111 999 A101 2014-01-01 00:00:00.000 D1 114 996 A104 2014-01-04 00:00:00.000 D2 115 995 A105 2014-01-05 00:00:00.000 D2 

Note How to update specified names to specific dates in one ad without a loop.

Answer 1

One way to imagine this (perhaps not the fastest) uses UPDATE with a CASE statement.

 UPDATE test SET name = CASE WHEN col4 BETWEEN '2014-01-01' AND '2014-01-02' THEN 'D1' WHEN COL4 BETWEEN '2014-01-04' AND '2014-01-06' THEN 'D2' ELSE name --Keeps que old value if doesn't match any case statement END 

I tested here and it worked exactly as you needed.