1

im trying to update table temp(F4) result from bottom to top. Is it while-loop every row and update can be order by?

enter image description here

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2286756
  • 211
  • 1
  • 2
  • 11
  • Your question makes no sense. Which column are you updating? What are you updating it to? What's the update critera? Please post a sample result for this table – Nick.Mc Dec 18 '16 at 12:34
  • 1
    There is no **intrinsic** meaning to the concepts of **_top/bottom of a database table_**. A table is a storage of information and you set its only order upon retrieval (which may take following any legal rule for one or more fields). There are "tools" (i.e. keys) that can be defined on behalf of data extraction efficiency, and these keys can be used as part of the sorting paradigm. – FDavidov Dec 18 '16 at 12:43
  • Looks like you are ordering by `row` and you want to update F4 to the next non zero value? Is that correct? – Martin Smith Dec 18 '16 at 12:50

1 Answers1

-1

Assuming ROW is a positive integer then you can use (demo)

WITH T1 AS
(
SELECT *, 
       MIN(CASE WHEN F4 <> 0 THEN CONCAT(FORMAT([ROW],'D10'),[F4]) END) 
         OVER (ORDER BY [ROW] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS M
FROM temp

)
SELECT *, 
       CAST(SUBSTRING(M,11,11) AS INT) AS val
FROM T1
ORDER BY [ROW]

If you want to update F4 you can use

WITH T1 AS
(
SELECT *, 
      MIN(CASE WHEN F4 <> 0 THEN CONCAT(FORMAT([ROW],'D10'),[F4]) END) 
           OVER (ORDER BY [ROW] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS M
FROM temp

)
UPDATE T1
SET F4 = COALESCE(SUBSTRING(M,11,11),0);
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Hi @Martin, Thanks for replying. This is what I need. Not really familiar with this statement (CURRENT ROW AND UNBOUNDED FOLLOWING). – user2286756 Dec 19 '16 at 02:28
  • This question lacks critical clarity, therefore it is impossible to provide an exact answer. – Tim Biegeleisen Feb 27 '20 at 10:08
  • @TimBiegeleisen - it is possible to infer the spec from the desired result and the comment above "This is what I need" shows the inferrence was correct – Martin Smith Feb 27 '20 at 10:15