Clever Geek Handbook

How do I scan each row of a table and update the current row based on the previous row?

I need to update the current line using the following logic:

  • if the current row is NULL then set it as the previous row
  • if the current line is not null then no action

the first row is not NULL, then NULL appears randomly

These NULLs need to be updated using the previously mentioned logic

eg.

 1. 1
 2. null
 3. null
 4. 2
 5. null
 6. null

needs to be updated as

 1. 1
 2. 1
 3. 1
 4. 2
 5. 2
 6. 2

How do I do this in SQL?

Thanks g

+3


source to share


5 answers 


while (select count(*) FROM Table_1 where c1_derived = '') > 0
begin
    update top(1) Table_1
    set c1_derived = (select c1_derived from Table_1 t2 where (t2.id = [Table_1].id-1))
    where c1_derived = ''
end


0


source


In the case of two values Null

in a row, you need to define the smallest non-null table value, so I think this Outer Apply

will handle your problem:

CREATE TABLE #TB(ID Int Identity(1, 1), Value Int)
INSERT INTO #TB([Value]) VALUES(1),(Null),(Null),(2),(Null),(Null)

UPDATE G SET G.Value = GG.Value
FROM 
    #TB AS G
        OUTER APPLY
    (SELECT
        TOP 1 *
    FROM
        #TB AS GG
    WHERE
        GG.Value IS NOT NULL
        AND
        GG.ID < G.ID
    ORDER BY
        GG.ID DESC
    ) AS GG
WHERE
    G.Value IS NULL

SELECT * FROM #TB AS T


but note that if the first is the value Null

, it won't give you any results, since you haven't defined the logic for this scenario.

0


source


This can help:

    SELECT
      t1.col1,
      t1.col2 AS previous,
      (SELECT
        t2.col2
      FROM table_1 t2
      WHERE t2.col1 = (SELECT
        MAX(t3.col1)
      FROM table_1 t3
      WHERE t3.col1 <= t1.col1
      AND col2 IS NOT NULL))
      AS new
    FROM table_1 t1;

result

output

0


source


Where are you using this SQL code? If you are using Hive SQL, for example, there is a function that allows you to directly get the last non-null value:

LAST_VALUE(col, true) over (PARTITION BY id ORDER BY date)

Oracle 10g also has a feature for this, as described in this thread:

Fill in blanks with last non-zero sum - Oracle SQL

Are you familiar with window functions?

0


source


Try below script. (sql 2008+)

CREATE TABLE #table(id Int Identity(1, 1), value Int)
INSERT INTO #table([Value]) VALUES(1),(Null),(Null),(2),(Null),(Null)


;WITH cte AS
 (
    SELECT ID,Value,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS row
    FROM #table
 ) 
 SELECT a.ID,max(b.Value) 
FROM cte a
    INNER JOIN cte b ON a.row >=b.row
 GROUP BY a.ID

 drop table #table

Edit2 is also another script using "UNBOUNDED PRECEDING" 

CREATE TABLE #table(id Int Identity(1, 1), value Int)
INSERT INTO #table([Value]) VALUES(1),(Null),(Null),(2),(Null),(Null)

select *   ,max(t.value)  over(order by Id Rows UNBOUNDED PRECEDING) maxValue
from #table t


drop table #table

check this link about "OVER Clause" https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

0


source






More articles:


All Articles