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 |
|
dsetzer
Starting Member
31 Posts |
 Posted - 2002-06-03 : 10:24:00
|
| Ok, this is an extension to a private e-mail that turned into a posting on the ASPMessageboard.com. The link to the posting is:http://www.aspmessageboard.com/forum/databases.asp?M=422987&F=21&P=1I have a table that I need to do an initial population of an Amount column. I know that the first value in the table is going to be 1000.00. Then, the next record (by a descending TypeID and then a descending StepID) is going to be 1000.00 * AnotherField.I know that, when doing an update statement, you can do:UPDATE TableSET @variable = Field = ExpressionBut, this isn't working for me because I can't specify what order the updates should occur.So, the final question is - is there a way to specify an ORDER BY for an UPDATE statement? Or, what are suggestions as to a work around for a problem like this? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-03 : 10:55:57
|
| I believe if you select into a temp table with an order by clause and then update your temp table it'll retain its order . . .It must if rrb's little trick here works ...Then you can update your base table from the temp table...<O> |
 |
|
|
dsetzer
Starting Member
31 Posts |
Posted - 2002-06-03 : 12:01:40
|
| I didn't find a way to do an ORDER BY with an update. So, I created a temporary table, added an IDENTITY field and populated it with the primary key from the source table. When I populated it, I ordered the records in the way that I wanted. I then created a loop that ran until this temp. table was empty. Each iteration I got the next item, and removed it from the table. I did what I needed to do with it and then started over. It's odd that you can't order an UPDATE statement. Oh, well. At least I didn't use a cursor. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-03 : 12:19:27
|
quote:
It's odd that you can't order an UPDATE statement. Oh, well. At least I didn't use a cursor.
It's not really odd. Relational data relies entirely on the contents/values/attributes of the data that you store, not the way in which that data is stored or accessed. An ORDER BY has no bearing on the data's value. Think of a bag of marbles, each row as a marble; there's no way to store the marbles in a particular "order" inside the bag. The only way to identify one marble from the next is by color, markings, or material (metal, glass, plastic) Numbering the marbles is not the same as a row number or order, because that number is assigned regardless of how the marbles are selected.Another (hopefully useful this time)  analogy is musical chairs. The order in which they sit down can change from one round to the next, but the people are the same, and the chairs are the same. The order doesn't change the clothes they wear, the color of their hair, whether one chair has a rickety leg, etc.Things like running totals and such that rely on an order of processing are best done using cursors. Take a look at Garth's running total article:http://www.sqlteam.com/item.asp?ItemID=3856 |
|
|
|
|
|
|
|
|
ORDER BY in an UPDATE?