SQL how to update a row from a row above?

user_7387622 0
Viewed 72

I have a table with products configuration.

enter image description here

Id_conf are connected with products (colspan_title) by row with a title and numbers of rows in colspan_no i.e. id_conf 4461-4465 are connected with colspan_title from a row 4461.

How to write a query which join every id_conf with colspan_title? Is it possible to update a cell from a cell above?

Data in text:

id_conf position    proj_id colspan_no  colspan_title  
4460    1           575     1           prod1  
4461    2           575     5           prod2  
4462    3           575     0     
4463    4           575     0     
4464    5           575     0     
4465    6           575     0     
4466    7           575     2           prod3  
4467    8           575     0     
4468    9           575     3           prod4  
4469    11          575     0     
4470    10          575     0     
4471    12          575     3           prod5  
4472    14          575     0     
4473    13          575     0

EXPECTED OUTPUT all id_conf have colspan_title

Thanks DS

sql-server sql
3 Answers

You have position column also, which can be used as follows to update the colspan_title of all rows where colspan_no is 0:

update your_table t
set t.colspan_title = (select tt.colspan_title from your_table tt
                        where tt.colspan_no > 0 and t.proj_id = tt.proj_id
                          and t.position between tt.colspan_no 
                                             and tt.colspan_no + tt.position - 1)
where t.colspan_no = 0
user_115656290

You can use window functions SUM() and FIRST_VALUE() in an updatable CTE:

WITH cte AS (
  SELECT *, FIRST_VALUE(colspan_title) OVER (PARTITION BY grp ORDER BY id_conf) title
  FROM (
    SELECT *, SUM(colspan_no) OVER (ORDER BY id_conf) grp
    FROM tablename
  ) t  
)
UPDATE cte 
SET colspan_title = title
WHERE colspan_no = 0

If the order of the rows is not defined by id_conf but by position change ORDER BY id_conf in the OVER clauses of both SUM() and FIRST_VALUE() with ORDER BY position.

See the demo.
Results:

> id_conf | position | proj_id | colspan_no | colspan_title
> ------: | -------: | ------: | ---------: | :------------
>    4460 |        1 |     575 |          1 | prod1        
>    4461 |        2 |     575 |          5 | prod2        
>    4462 |        3 |     575 |          0 | prod2        
>    4463 |        4 |     575 |          0 | prod2        
>    4464 |        5 |     575 |          0 | prod2        
>    4465 |        6 |     575 |          0 | prod2        
>    4466 |        7 |     575 |          2 | prod3        
>    4467 |        8 |     575 |          0 | prod3        
>    4468 |        9 |     575 |          3 | prod4        
>    4469 |       11 |     575 |          0 | prod4        
>    4470 |       10 |     575 |          0 | prod4        
>    4471 |       12 |     575 |          3 | prod5        
>    4472 |       14 |     575 |          0 | prod5        
>    4473 |       13 |     575 |          0 | prod5        
>    4474 |       15 |     575 |          3 | prod6        
>    4475 |       17 |     575 |          0 | prod6        
>    4476 |       16 |     575 |          0 | prod6
user_104988280

If you want to "fill in" the NULL values with the previous value, I would suggest:

with toupdate as (
      select t.*,
             max(colspan_title) over (partition by grp order by id_conf) as new_colspan_title
      from (select t.*,
                   count(colspan_title) over (order by id_conf) as grp
            from t
           ) t
     )
update toupdate
    set colspan_title = new_colspan_title
    where colspan_title is null;

Note that this version only uses colspan_title. It does not depend on NULL values also have colspan_no = 0.

user_11440350
Related