更新多行而不循环 [英] Update multiple rows without looping

问题描述

我想在不使用循环的情况下在更新语句中一次更新多行.

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:

表格:

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

插入:

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:

为2014-01-01 和2014-01-02
之间的日期更新name D1为 2014-01-04 和 2014-01-06

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

预期结果表:

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.

推荐答案

我能想到的一种方法(可能不是最快的方法)是使用带有 CASE 语句的 UPDATE.

One way I can imagine of doing that (maybe not the fastest one) is using a 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 have tested here and it worked exactly the way you need.

这篇关于更新多行而不循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！