I want to update multiple rows at once in a update statement without using loop.
I have the following table with some records as shown below:
Table:
create table test
(
col1 int,
col2 int,
col3 varchar(20),
col4 datetime,
name varchar(max)
);
Insertion:
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 the specific dates for example as shown below:
Update name
D1 for the date between 2014-01-01
and 2014-01-02
Update name
D2 for the date between 2014-01-04
and 2014-01-06
Expected Result 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 can I update the above names to specific dates in a single update statement without looping.