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.
| Author |
Topic |
|
function
Starting Member
12 Posts |
 Posted - 2013-01-22 : 11:19:03
|
| Greetings fellows,If i have an array of integers (say 1 to 10), how can i update a table of ten rows with a single update query with the values of this array (would not like to use "for" statement...).e.g. if the array list is [3,2,1,4,6,7,5,8,10,9] then i would like the update query to result to thisCOLUMN-A----------COLUMN-B==========================George-------------3Nick-------------2JOhn-------------4Mike-------------6GUs-------------7Maria-------------5Mary-------------8Nike-------------10Michael-------------9THanks in advance. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 11:46:33
|
| Is the omission of the number 1 in column-B just a typo?Assuming that it is, how is the array stored? If it is in a table (which would be the ideal), then you would also need to specify the ordering - i.e., something that tells SQL Server that 3 should come first, and 9 should come last etc. So it should be in a table with two columns - ordering_id and number.The next question is where are the names from Column-A stored? Again, you need an ordering scheme to tell that George comes first and Michael is last.If all of that is true, a single update statement can do the updates. Can you answer the questions above about where the information is stored and how to order it? |
 |
|
|
function
Starting Member
12 Posts |
Posted - 2013-01-22 : 12:02:36
|
| No,The array list is just a vb array list not coming from some database.The only values that are pre-stored in a table are the names with column b equaling = null.Unfortunately there is no order column in the table that stores the names. So now that i am thinking again, maybe a single update query does not do the job. I think i need a for statement....But if i use a for statement, i think i will need an UPDATE TABLE TOP 1 etc.Can this be done? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-22 : 12:55:19
|
| Data stored in a database table has no inherent order to it. Unless you can provide some rule for ordering (using an order by clause in your select query), the results that you get will not be ordered in any predictable manner.So whether you use a for loop (which I would advise against), or you use a set based query to do the update unless there is a rule for ordering that you can provide, this cannot be done in a reliable manner. |
|
|
|
|
|
|
|
|
Single Update query