October 19, 2011 at 4:40 am
Hi,
how to update null with previous value.
ITm Field1 Result
254689 AAAA aaaa
254690 null aaaa
254691 null aaaa
254694 bbbb bbbb
254695 null bbbb
254696 null bbbb
254697 null bbbb
254700 cccc cccc
254701 null cccc
254702 null cccc
Thanks
October 19, 2011 at 4:57 am
I guess you want something like this?
update t1 set column = t2.column
from
table t1
inner join table t2 on (t2.id = (select max(id) from table where id < t1.id and column is not null))
where t1.column is null
Tip: This may take ages without support from a proper index.
Edit: Added where clause
October 19, 2011 at 5:18 am
This is likely to be more efficient as the solution given creates a "triangular join", search this site for an explanation...
;with cte as (
select <Fields>
, row_number() over (order by <KeyField>) as JoinKey
)
update main
set <main.Field> = <offset.Field>
from cte main
join cte offset
on main.JoinKey = offset.JoinKey - 1
Notes:
<KeyField> should ideally be the first column of your clustered index. Failing that, a covering index with <KeyField> as the first field and including the column to be updated should be added.
Regards, Iain
October 19, 2011 at 5:26 am
I agree, the cte should be more efficient.
There should be checks added though for main.JoinKey IS NULL and offset.JoinKey IS NOT NULL?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
October 19, 2011 at 6:32 am
Agreed, CTE is most likely more efficient, and proper use of filters should definitely be used. A CTE with row_number does not take into consideration that there may be multiple rows with nulls, therefor I used my approach, where I (not completely according to the text, but how I understood it) update with the value from the last row having a non null value.
October 19, 2011 at 7:11 am
I don't know this is the best solution or not, but you will get the results:
DECLARE @table as TABLE (col1 int, col2 varchar(5), col3 varchar(5))
INSERT INTO @table values (254689, 'AAAA', 'aaaa')
INSERT INTO @table values (254690, null, 'aaaa')
INSERT INTO @table values (254691, null, 'aaaa')
INSERT INTO @table values (254694, 'bbbb', 'bbbb')
INSERT INTO @table values (254695, null, 'bbbb')
INSERT INTO @table values (254696, null,'bbbb')
INSERT INTO @table values (254697, null, 'bbbb')
INSERT INTO @table values (254700, 'cccc', 'cccc')
INSERT INTO @table values (254701, null, 'cccc')
INSERT INTO @table values (254702, null, 'cccc')
SELECT * from @table
DECLARE @coltmp AS VARChAR(5)
UPDATE @table SET col2 = t4.Newcol2
FROM @table t3
INNER JOIN
(SELECT t.col1, (SELECT TOP 1 col2 FROM @table t1 WHERE col2 IS NOT NULL AND t1.col1 < t.col1 ORDER BY col1 DESC) Newcol2
FROM @table t WHERE t.col2 IS NULL) t4
ON t3.col1=t4.col1
WHERE t3.col2 IS NULL
SELECT * FROM @table
October 19, 2011 at 7:41 am
okbangas (10/19/2011)
A CTE with row_number does not take into consideration that there may be multiple rows with nulls
Unlikely if this is the key field being used to determine which is the "next" row I would think? Otherwise there is no way of determining the "next" row and the update could possibly use a different value each time.
Agreed though, the OP should ensure that the field being used to determine the "next" row should not contain nulls. 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply