Click here to Skip to main content
15,915,611 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

VB
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:

VB
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:
VB
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 ?
Posted
Updated 15-Oct-12 4:02am
v4
Comments
Tejas Vaishnav 15-Oct-12 9:18am    
please give more detail, it will quite confusing...
DanaH85 15-Oct-12 9:50am    
I tried to clarify myself

Load all your changes in a table(say TableChanges), and create update statements by SQL JOIN for MainTable

SQL
UPDATE T1
    SET T1.Num = T2.Num
FROM MainTable AS T1
INNER JOIN TableChanges AS T2
    ON T1.ID = T2.ID
 
Share this answer
 
Comments
Tejas Vaishnav 17-Oct-12 4:13am    
my 5+ Very nice.
Kuthuparakkal 17-Oct-12 13:57pm    
thanks Thejas
Maciej Los 17-Oct-12 13:56pm    
Good work, +5!
Kuthuparakkal 17-Oct-12 13:57pm    
thank you Mr Los
Herman<T>.Instance 23-Oct-12 5:24am    
that's it!
Demonstration of Kuthuparakkal query. you need to make some changes as per your requirement.

SQL
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;
 
Share this answer
 
Thank you all

I found a way to do so.
I created a new table with 2 columns: OldVal, NewVal
and then ran the following script:

SQL
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
 
Share this answer
 
Comments
Herman<T>.Instance 23-Oct-12 5:26am    
you are using a cursor. Remember that using a cursor can give bad performance. The earlier given solutions with the Insert based on JOINS are better options.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900