SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Single Update query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

function
Starting Member

12 Posts

Posted - 01/22/2013 :  11:19:03  Show Profile  Reply with Quote
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 this
COLUMN-A----------COLUMN-B
==========================
George-------------3
Nick-------------2
JOhn-------------4
Mike-------------6
GUs-------------7
Maria-------------5
Mary-------------8
Nike-------------10
Michael-------------9

THanks in advance.

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 01/22/2013 :  11:46:33  Show Profile  Reply with Quote
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?
Go to Top of Page

function
Starting Member

12 Posts

Posted - 01/22/2013 :  12:02:36  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 01/22/2013 :  12:55:19  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000