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
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
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.
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
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?
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