UPDATE语句的妙用

前几天一同事问我怎么样用简单的SQL语句UPDATE如下记录：

ID Category

——————-

1 A

2

3 B

4

5

6 C

7

UPDATE成：

ID Category

——————-

1 A

2 A

3 B

4 B

5 B

6 C

7 C

其中ID是按照从小到大顺序排列的。

当然该问题用游标很容易实现，类似如下：

DECLARE @ID INT

DECLARE @Category VARCHAR(10)

DECLARE Cur_user CURSOR

FOR SELECT ID,Category FROM Mosfet$

OPEN Cur_user

FETCH NEXT FROM Cur_user INTO @ID,@Category
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Category <>” set @Category1 = @Category
UPDATE Mosfet$ SET Category = @Category1 where ID = @ID
FETCH NEXT FROM Cur_user INTO @ID,@Category
END
DEALLOCATE Cur_user

但当记录一多的时候效率太差，那怎么实现快速更新呢？我们知道UPDATE时SQL SERVER是一条一条进行UPDATE的，我们仔细看看表的数据结构，其实只要在UPDATE时能有个变量保存上一条记录的Category的值就能解决问题，那UPDATE语句有没有这个功能呢？查一下帮助：

UPDATE
{
table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]

{ { [ FROM { < table_source > } [ ,...n ] ]

[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,...n ] ) ]

看SET部分：

@variable

已声明的变量，该变量将设置为 expression 所返回的值。

SET @variable = column = expression 将变量设置为与列相同的值。这与 SET @variable = column, column = expression 不同，后者将变量设置为列更新前的值

注意粗体部分，正好符合我们的要求:)

简单的语句如下：

DECLARE @Category VARCHAR(10)
SELECT @Category=(SELECT TOP 1 Category FROM TEST WHERE Category<>”)–取第一条非空记录的Category的值
UPDATE Test

SET @Category=CASE WHEN CATEGORY =” THEN @Category ELSE CATEGORY END,CATEGORY=@Category
FROM Test

测试如下：

CREATE TABLE TEST
(ID INT,
Category VARCHAR(10))

INSERT INTO TEST
SELECT 1,’A’
UNION ALL
SELECT 2,”
UNION ALL
SELECT 3,’B’
UNION ALL
SELECT 4,”
UNION ALL
SELECT 5,”
UNION ALL
SELECT 6,’C’
UNION ALL
SELECT 7,”

SELECT *FROM TEST

ID Category
———– ———-
1 A
2
3 B
4
5
6 C
7

DECLARE @Category VARCHAR(10)
SELECT @Category=(SELECT TOP 1 Category FROM TEST WHERE Category<>”)–取第一条非空记录的Category的值
UPDATE Test
SET @Category=CASE WHEN CATEGORY =” THEN @Category ELSE CATEGORY END,CATEGORY=@Category
FROM Test

SELECT * FROM TEST

ID Category
———– ———-
1 A
2 A
3 B
4 B
5 B
6 C
7 C

结果符合我们的要求，而且性能很好。

当然该语句还是有点问题的，在哪里呢？就是数据返回的顺序问题，我们知道按照关系型数据库理论，表里面的数据严格来说是没有顺序的，而该语句要成功执行就有赖于数据的顺序，那怎么让UPDATE按我们的顺序进行UPDATE呢？关于这个问题，我会再详细说明，这里给出一个方法，在ID上建一个集群索引，这个索引按照ID从小到大排序，这样就能确保数据按ID从小到大的顺序进行UPDATE。

CREATE CLUSTERED INDEX ID_IX ON TEST(ID ASC)

其实该问题并不复杂，只要熟悉SQL语句的用法及认真研究需求就能写出简单高效的SQL语句，这让我想起TOM的名言：

能用一条SQL语句实现的就尽量用一条语句去实现。