help for batch update - 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 2005 Forums

Transact-SQL (2005)

help for batch update

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2007-09-10 : 05:24:59

hai,
i have a requirement in which i need to do the batch update to the database.
so in a UI, i have 5 records. in all the ui user can edit one cloumn and that one column i need to update in a single stretch.
ex:

eno ename DOJ
1 Riyaz 2006-1

2-12
2 alex 2006-11-11
3 Alen 2006-10-10.
DOJ can be edited by the user. if the user i want to take all the values in single stretch by concatinating into a string..
say edited date is 2006-09-09,2006-09-08,2006-09-07 respctively.
so my concatinated string will be 1&2006-09-09,2&2006-09-08,3&2006-09-07.
finally how can i update this into the table without using any sting to search for the id.
help pls.

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 10:27:33

You can use a SPLIT function to "JOIN" the DOJ values to the original data, and make an Update accordingly.

So something like:


UPDATE U
SET U.DOJ = S.VALUE
FROM MyTable AS U
     JOIN dbo.My_FN_Split('2006-09-09,2006-09-08,2006-09-07') AS S
         ON S.Item = U.Item

It just so happens that your "eno" is 1,2,3 but typically, from a Web page, you need to get two "lists". The first is the IDs of the records, the second is the Changed Data (DOJ in this case), so you would more likely do:


UPDATE U
SET U.DOJ = S.VALUE
FROM MyTable AS U
     JOIN dbo.My_FN_Split('1,2,3') AS IDs
         ON IDs.Value = U.eno
     JOIN dbo.My_FN_Split('2006-09-09,2006-09-08,2006-09-07') AS S
         ON S.Item = IDs.Item

For Split Functions see here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functions,Splitting%20delimited%20lists

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-11 : 05:57:02

Also refer http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources