在SQL上使用where语句进行大规模更新

问题描述

我必须为大量更新sql表编写脚本.
问题在于更新包含where语句.

我会举一个例子来解释.
我有一个包含2列的表格:ID，数字
现在，我在Num列上有一个更改列表:

Hi,

I have to write a script for massive update sql table.
The problem is the update includes a where statement.

I''ll explain by giving an example.
I have a table with 2 columns: ID, Num
Now I have a list of changes to do on the Num column:

old value | new value
----------------------
1         | 100
2         | 3000
3         | 417
4         | 85
5         | 662
6         | 995

测量:
更改前的表将具有以下数据:

Measning:
The table before changes will have the following data:

ID | Num
--------
1  | 1
2  | 1
3  | 2
4  | 3
5  | 3
6  | 3
7  | 3
8  | 3
9  | 4
10 | 5
11 | 6
12 | 6
13 | 6

现在，在进行大规模更新之后，我将得到如下所示的表:

Now after the massive update I''ll have the table looking like that:

ID | Num
--------
1  | 100
2  | 100
3  | 3000
4  | 417
5  | 417
6  | 417
7  | 417
8  | 417
9  | 85
10 | 662
11 | 995
12 | 995
13 | 995

没有规律性，所以我无法计算...
而且我不想写那么多声明"的

反正有这样做吗?

There''s no regularity so I can''t calculate it...
And I don''t want to write so many "declare"s

Is there anyway to do that ?

推荐答案

UPDATE T1
    SET T1.Num = T2.Num
FROM MainTable AS T1
INNER JOIN TableChanges AS T2
    ON T1.ID = T2.ID

DECLARE @CHANGES  AS TABLE (
	OLDVALUE INT
	,NEWVALUE INT
);

DECLARE @MAINTABLE AS TABLE(
	ID INT
	,NUM INT
);

INSERT INTO @CHANGES VALUES (1,100);
INSERT INTO @CHANGES VALUES (2,3000);
INSERT INTO @CHANGES VALUES (3,417);
INSERT INTO @CHANGES VALUES (4,85);
INSERT INTO @CHANGES VALUES (5,662);
INSERT INTO @CHANGES  VALUES (6,995);


INSERT INTO @MAINTABLE VALUES (1,1);
INSERT INTO @MAINTABLE VALUES (2,1);
INSERT INTO @MAINTABLE VALUES (3,2);
INSERT INTO @MAINTABLE VALUES (4,3);
INSERT INTO @MAINTABLE VALUES (5,3);
INSERT INTO @MAINTABLE VALUES (6,3);
INSERT INTO @MAINTABLE VALUES (7,3);
INSERT INTO @MAINTABLE VALUES (8,3);
INSERT INTO @MAINTABLE VALUES (9,4);
INSERT INTO @MAINTABLE VALUES (10,5);
INSERT INTO @MAINTABLE VALUES (11,6);
INSERT INTO @MAINTABLE VALUES (12,6);
INSERT INTO @MAINTABLE VALUES (13,6);

--SELECT * FROM @CHANGES;
SELECT * FROM @MAINTABLE;

UPDATE M SET M.NUM = C.NEWVALUE
FROM @MAINTABLE AS M
	INNER JOIN @CHANGES AS C ON M.NUM = C.OLDVALUE

SELECT * FROM @MAINTABLE;

declare cur cursor for select oldVal,newVal from changeVals

declare @OldVal int
declare @NewVal int

OPEN cur
FETCH NEXT FROM cur INTO @OldVal, @NewVal

WHILE @@FETCH_STATUS = 0
BEGIN
        update table set column = @NewVal where column = @OldVal
        FETCH NEXT FROM cur INTO @OldVal, @NewVal
END

CLOSE cur
DEALLOCATE cur

这篇关于在SQL上使用where语句进行大规模更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！