Bulk Update In Same Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bulk Update In Same Table

Author

Topic

AnurRS
Starting Member

2 Posts

Posted - 2012-06-25 : 22:22:43

Can we bulk update the records of the same table.
I have a table structure like this.
Table Name : MyTable

SeqID Name SortNo
1 Test1 0
2 Test2 1
3 Test3 2
4 Test4 0
5 Test5 1
6 Test6 2
7 Test7 3

SeqID : Unique Auto generate column
Name : Any arbitary name
SortNo : Index order in which the name is to be shown

From my code i get all the list and with some logic, i regenerate all the sort numbers again which should be unique.
lets say my required output is
SeqID Name SortNo
1 Test1 0
2 Test2 1
3 Test3 2
4 Test4 3
5 Test5 4
6 Test6 5
7 Test7 6

---
How can i update all the sort no value in the table . Do i need to loop each record and update ? Or can i first update all the records and then finally commit the updated transaction.

Best Regards
Anur

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 22:31:54

We need to know what your logic is behind SortNo. Right now you can just do SeqID-1 to get it, but I suspect you aren't telling us the full story yet. We need the full story to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AnurRS
Starting Member

2 Posts

Posted - 2012-06-26 : 00:20:09

Tara Kizer ..Thanks for the reply and concern.

SortNo as mentioned is the index or the order according to which the Name column is to be displayed in the front end GUI list view.
For further detail info, i have one more column as Value .

The work i need to do is like this..
Originally let Database name : DatabaseVer1 MyTable has only these data
SeqID Name SortNo Value
1 Test1 0 2
2 Test2 1 4
3 Test3 2 6

Then from one more similar table of database named : DatabaseVer2 , there is similar table MyTable with the data as follows
SeqID Name SortNo Value
1 Test4 0 8
2 Test5 1 10
3 Test6 2 12
4 Test7 3 14

Now i need to copy the data from databaseVer1 MyTable to DatabaseVer2 MyTable. With some merging logic i have copied the data whose result is shown in the above post.
SeqID Name SortNo Value
1 Test1 0 2
2 Test2 1 4
3 Test3 2 6
4 Test4 0 8
5 Test5 1 10
6 Test6 2 12
7 Test7 3 14

After merging data, i once again retrived all those data and on the basis of Value column, i once again calculated the new SortNo. with some sorting algorithm.
After getting the desired sortNo values, i want to now update this value in the table. So, can i do the update in bulk or do i need to carry out the update for each record row.

Best Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources