0

I want to learn how to process table rows when updating column(s), in a certain order (and not in the random or in the order that the records were created).

To illustrate my question, in the made-up example below I want to set the Ranking according to the date_time such that the older records are processed first (and get lower ranking).

create table #testTable (customer_id char(20), ranking int, date_time
Datetime, pk_value int identity )
go

insert into #testTable (customer_id, date_time) values 
('MICROSOFT', '20110202')
insert into #testTable (customer_id, date_time) values 
('MICROSOFT', '20120202')
insert into #testTable (customer_id, date_time) values
('MICROSOFT', '20090512')
go
declare @Rank int
set @Rank = 0
update #testTable set @Rank = @Rank + 1, ranking = @Rank
Hidalgo
  • 941
  • 2
  • 14
  • 38
  • You should label your question with the database you are using. From the syntax, I surmise that it is SQL Server. – Gordon Linoff Mar 08 '15 at 14:29
  • You can always do `UPDATE table SET column="value" WHERE foo = bar ORDER BY any_column;` – Ifedi Okonkwo Mar 08 '15 at 14:29
  • @GordonLinoff Yes, you are right, I meant SQL Server. I will do it next time. – Hidalgo Mar 08 '15 at 14:36
  • @IfediOkonkwo Thank you for your suggestion. When I try your syntax I get error "Incorrect syntax near ORDER" – Hidalgo Mar 08 '15 at 14:37
  • @Hidalgo Is it possible to post your code, where it gave "Incorrect syntax" error? – Ifedi Okonkwo Mar 08 '15 at 14:54
  • @IfediOkonkwo Add the following to my UPDATE .. example above: where customer_id = 'MICROSOFT' order by date_time and I get the error. – Hidalgo Mar 08 '15 at 14:56
  • @Hidalgo. Okay, I'm sorry. Now that it's clear you're talking about SQL Server in particular, it seems that the solution is not quite as simple: http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by. My suggestion above certainly pertains to MySQL. You may have no choice than to spend some effort along the line suggested by GordonLinoff. – Ifedi Okonkwo Mar 08 '15 at 15:04
  • @IfediOkonkwo Thank you for the reference to another post. I am beginning to see the light. – Hidalgo Mar 08 '15 at 15:31
  • One of the fundamentals of database normalization is to not store calculated values. That being the case, why do you want to do so? – Dan Bracuk Mar 08 '15 at 16:17
  • @DanBracuk The above example was just an example. The actual case involves the table where the programs stores in-stock quantities of a certain item, different row storing a quantity at a different price received at different time. When taking a certain quantity of the item from stock, I need to reduce the in-stock quantity (hence UPDATE the in-stock table) in the order of older records first (FIFO). Therefore, my UPDATE needs to process records in the order of the In-Date. – Hidalgo Mar 08 '15 at 20:23

1 Answers1

3

It looks like you are using SQL Server. If so, you can set the ranking using the (appropriately named) ranking functions:

with toupdate as (
      select t.*, row_number() over (order by date_time) as seqnum
      from #testtable t
     )
update toupdate
    set ranking = seqnum;
Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, I will have to study to understand your code (new syntax to me). Thank you for your suggestion. – Hidalgo Mar 08 '15 at 14:44
  • Gordon, what is the purpose or why do you have the "t.*" in your expression? – Hidalgo Mar 08 '15 at 15:33
  • Your answer using row_number() over is helpful (I learned something new). And it answers the question of the example I used to illustrate it. But my actual UPDATE is more complicated where I need to update not a "ranking" column but some quantity column. And it has to be done in a certain order. So using "seqnum" does not work for me. I should have created a different example to illustrate my question. Thank you. – Hidalgo Mar 08 '15 at 16:21
  • 2
    You should give this the check for answering the stated question – paparazzo Mar 08 '15 at 19:10
  • Hidalgo . . . If you have a different question, you should ask it as a new question (with sample data and desired results). You should not ask new questions in a comment. – Gordon Linoff Mar 08 '15 at 22:02