Inherit values from previous records

Question

I have the following table where all fields except Value as a part of a unique index on the table. I'd like to inherit the Value from a record with Value<>NULL to the next revision record only if that record has a NULL value (See example below):

Var[n]Value fields ate varchar and there maybe 1-n number of Var[n]value fields in my table.

Source:

Document#   Revision    Project#    config# Var1Value   Var2Value
1744    1   2   1   NULL    NULL
1744    2   2   1   NULL    NULL
1744    3   2   1   Tit1    ABC
1744    4   2   1   Tit2    ABD
1744    5   2   1   NULL    NULL
1744    6   2   1   NULL    SDC
1744    7   2   1       AS
1744    8   2   1   Tit3    NULL

Needed result (notice change for revision records 5 and 6 var1value and 5,8 for var2value):

Document#   Revision    Project#    config# Var1Value   Var2Value
1744    1   2   1   NULL    NULL
1744    2   2   1   NULL    NULL
1744    3   2   1   Tit1    ABC
1744    4   2   1   Tit2    ABD
1744    5   2   1   Tit2    ABD
1744    6   2   1   Tit2    SDC
1744    7   2   1       AS
1744    8   2   1   Tit3    AS

Any idea how to handle it by SQL?

Please advise.

I tried the following:

declare @TableName as VarChar(32) = 'MYTABLE'
declare @SetClause as VarChar(1024)
declare @LWhereClause as VarChar(1024)
declare @RWhereClause as VarChar(1024)

-- Get the column names.
select Column_Name
  into #Columns
  from Information_Schema.Columns
  where Table_Name = @TableName and Column_Name like 'Var%'
--select * from #Columns

-- Assemble the clauses we'll need for the   UPDATE   statement.
declare @ColumnName as VarChar(32)
while ( @@RowCount > 0 )
  begin
  select top 1 @ColumnName = Column_Name
    from #Columns
    order by Column_Name
  set @SetClause = case when @SetClause is NULL then '' else @SetClause + ', ' end +
    @ColumnName + ' = Coalesce( L.' + @ColumnName + ', R.' + @ColumnName + ' )'
  set @LWhereClause = case when @LWhereClause is NULL then '' else @LWhereClause + ' or ' end +
    'L.' + @ColumnName + ' is NULL'
  set @RWhereClause = case when @RWhereClause is NULL then '' else @RWhereClause + ' or ' end +
    'R.' + @ColumnName + ' is not NULL'
  delete from #Columns
    where Column_Name = @ColumnName
  end

--select @SetClause, @LWhereClause, @RWhereClause

-- Put together the   UPDATE   statement.
declare @Update as nVarChar(max)
set @Update=''
set @Update=@Update +
  'update L set ' + @SetClause + ' from ' + @TableName +
  ' as L inner join ' + @TableName + ' as R on R.DocId = L.DocId and R.Rev = L.Rev - 1 and R.Proj = L.Proj and R.Conf = L.Conf' +
  ' where ( ' + @LWhereClause + ' ) and ( ' + @RWhereClause + ' )'

-- Put together the entire loop.  This needs work.
declare @Loop as nVarChar(max)
set @Loop =''
set @Loop=@Loop+
  '@declare Eleanor as Int = 42;
    while ( @Eleanor > 0 ) ' 
    + @Update + ' 
    set @Eleanor = @@RowCount
    end'

--select @Loop

-- Execute it.
exec @Loop

drop table #Columns

and I get the following error on exec loop. Why it is truncating the nvarchar string?

Msg 203, Level 16, State 2, Line 53
The name '@declare Eleanor as Int = 42;
while ( @Eleanor > 0 ) update L set variable104 = Coalesce( L.variable104, R.variable104 ), variable105 = Coalesce( L.variable105, R.variable105 ), variable106 = Coalesce( L.variable106, R.variable106 ), variable107 = Coalesce( L.variable107, R.variable107 ), variable112 = Coalesce( L.variable112, R.variable112 ), variable116 = Coalesce( L.variable116, R.variable116 ), variable119 = Coalesce( L.variable119, R.variable119 ), variable120 = Coalesce( L.variable120, R.variable120 ), variable121 = Coalesce( L.variable121, R.variable121 ), variable122 = Coalesce( L.variable122, R.variable122 ), variable124 = Co' is not a valid identifier.

I am not sure what you mean by densed? revision is part of the unique index (composed of document#,Revision,project#,and config#). I need the updates to take place only based on previous revision's val[n] value if it is not null and the respective current revision's var[n]value is NULL. — user1491749, Jul 05 '12 at 19:30
"Dense" refers to a series of values without gaps. If the revision numbers are dense then, given a value R greater than 1, you can always count on the value R - 1 being in the table (for the same document and project). If the revisions aren't dense then you need to keep search for the maximum value less than R. There may not even be a revision one! — HABO, Jul 05 '12 at 21:54

HABO · Answer 1 · 2012-07-05T20:21:25.527

EDIT: Begging your pardon, but I failed to consider the variable columns.

This query will retrieve the columns defined in YourTable:

select Column_Name from Information_Schema.Columns where Table_Name = 'YourTable'

Thereafter, you need to build a dynamic query and EXEC it. You could craft code like that shown below for each individual column, or to process all of the columns in one go.

Building the query dynamically is a somewhat tedious process. The following should get you well on your way.

declare @TableName as VarChar(32) = 'YourTable'
declare @SetClause as VarChar(1024)
declare @LWhereClause as VarChar(1024)
declare @RWhereClause as VarChar(1024)

-- Get the column names.
select Column_Name
  into #Columns
  from Information_Schema.Columns
  where Table_Name = @TableName and Column_Name like 'Var%'
select * from #Columns

-- Assemble the clauses we'll need for the   UPDATE   statement.
declare @ColumnName as VarChar(32)
while ( @@RowCount > 0 )
  begin
  select top 1 @ColumnName = Column_Name
    from #Columns
    order by Column_Name
  set @SetClause = case when @SetClause is NULL then '' else @SetClause + ', ' end +
    @ColumnName + ' = Coalesce( L.' + @ColumnName + ', R.' + @ColumnName + ' )'
  set @LWhereClause = case when @LWhereClause is NULL then '' else @LWhereClause + ' or ' end +
    'L.' + @ColumnName + ' is NULL'
  set @RWhereClause = case when @RWhereClause is NULL then '' else @RWhereClause + ' or ' end +
    'R.' + @ColumnName + ' is not NULL'
  delete from #Columns
    where Column_Name = @ColumnName
  end

select @SetClause, @LWhereClause, @RWhereClause

-- Put together the   UPDATE   statement.
declare @Update as VarChar(4096) =
  'update L set ' + @SetClause + ' from ' + @TableName +
  ' as L inner join ' + @TableName + ' as R on R.DocId = L.DocId and R.Rev = L.Rev - 1 and R.Proj = L.Proj and R.Conf = L.Conf' +
  ' where ( ' + @LWhereClause + ' ) and ( ' + @RWhereClause + ' )'

-- Put together the entire loop.  This needs work.
declare @Loop as VarChar(4096) =
  '@declare Eleanor as Int = 42; ...' + @Update + '...'

select @Loop

-- Execute it.
exec @Loop

drop table #Columns

Here's one dreadful way that is based on revision numbers being dense:

declare @Docs as Table ( DocId Int, Rev Int, Proj Int, Conf Int, Var1 VarChar(10) Null, Var2 VarChar(10) Null )

insert into @Docs ( DocId, Rev, Proj, Conf, Var1, Var2 ) values
  ( 1744, 1, 2, 1, NULL, NULL ),
  ( 1744, 2, 2, 1, NULL, NULL ),
  ( 1744, 3, 2, 1, 'Tit1', 'ABC' ),
  ( 1744, 4, 2, 1, 'Tit2', 'ABD' ),
  ( 1744, 5, 2, 1, NULL, NULL ),
  ( 1744, 6, 2, 1, NULL, 'SDC' ),
  ( 1744, 7, 2, 1, '', 'AS' ), -- The example data for this row is unclear.
  ( 1744, 8, 2, 1, 'Tit3', NULL )

select * from @Docs

declare @Eleanor as Int = 42
while ( @Eleanor > 0 )
  begin
  update L
    set Var1 = Coalesce( L.Var1, R.Var1 ), Var2 = Coalesce( L.Var2, R.Var2 )
    from @Docs as L inner join
      @Docs as R on R.DocId = L.DocId and R.Rev = L.Rev - 1 and R.Proj = L.Proj and R.Conf = L.Conf
    where ( L.Var1 is NULL or L.Var2 is NULL ) and ( R.Var1 is not NULL or R.Var2 is not NULL )
  set @Eleanor = @@RowCount
  end

select * from @Docs

How would that work if I do not how many varvalues I have. In the example above I used var1 and var2, but when running my program it may run on a db with 100 variables (i.e var100) and in some cases with only few variables. I am looking for a dynamic way to process that even if it runs for hours. — user1491749, Jul 05 '12 at 19:27
I did edit my answer to include additional information on building a query dynamically. — HABO, Jul 06 '12 at 13:33

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

exact duplicate of : SQL QUERY replace NULL value in a row with a value from the previous known value p.s:i don't have privilege to comment.so i wrote as answer.

edited May 23 '17 at 11:45

Community

1
1

answered Jul 05 '12 at 18:27

abianari

343
7
19

Thank you Mahmood, I am looking for a dynamic way to make that query work for n number of varvalue fields in my example. Can you provide an example for doing that please? – user1491749 Jul 05 '12 at 19:14

Inherit values from previous records

2 Answers2