locked
Need to update particular column in particular row RRS feed

  • Question

  • I have table like this:

    LinN | RecType | Value -----+---------+-------- 1 | 01 | A12345 2 | 02 | Null 3 | 03 | Null 4 | 05 | Null 6 | 01 | B67890 7 | 02 | Null 8 | 09 | Null

    RecType 01 means new group of records starts.

    I need to make it look like this:

    LinN | RecType | Value -----+---------+-------- 1 | 01 | A12345 2 | 02 | A12345 3 | 03 | A12345 4 | 05 | A12345 6 | 01 | B67890 7 | 02 | B67890 8 | 09 | B67890

    Any help will be highly appreciated

    Wednesday, April 29, 2020 12:54 PM

Answers

  • Hi , 
    Please try following script  .

    CREATE TABLE mytable(
       LinN    VARCHAR(24) NOT NULL PRIMARY KEY
      ,RecType  VARCHAR(2) 
      ,Value   VARCHAR(7)
    );
    INSERT INTO mytable(LinN,RecType,Value) VALUES
     ('1','01','A12345')
    ,('2','02',NULL)
    ,('3','03',NULL)
    ,('4','05',NULL)
    ,('6','01','B67890')
    ,('7','02',NULL)
    ,('8','09',NULL);
    
    ;with cte as (
    select * , sum(case when [Value] is null then 0 else 1 end )over(order by LinN) ct
    from mytable)
    select LinN,RecType,max([Value])over(partition by ct)[Value]
    from cte
    /*
    LinN                     RecType Value
    ------------------------ ------- -------
    1                        01      A12345
    2                        02      A12345
    3                        03      A12345
    4                        05      A12345
    6                        01      B67890
    7                        02      B67890
    8                        09      B67890*/
     

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by LIONBAZ1 Thursday, April 30, 2020 4:37 PM
    • Marked as answer by Naomi N Thursday, April 30, 2020 7:46 PM
    Thursday, April 30, 2020 3:18 AM

All replies

  • Like this?

    -- Test Selection
    SELECT DST.*, (SELECT TOP 1 SRC.[Value] 
                   FROM yourTable AS SRC 
                   WHERE SRC.RecType = '01'
                         AND SRC.LinN < DST.LinN
                   ORDER BY SRC.LinN DESC) AS NewValue
    FROM yourTable as DST
    WHERE DST.RecType <> '01' AS [Value] IS NULL
    
    -- Update
    UPDATE DST
    SET [Value] = (SELECT TOP 1 SRC.[Value] 
                   FROM yourTable AS SRC 
                   WHERE SRC.RecType = '01'
                         AND SRC.LinN < DST.LinN
                   ORDER BY SRC.LinN DESC)
    FROM yourTable as DST
    WHERE DST.RecType <> '01' AS [Value] IS NULL


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 29, 2020 1:18 PM
  • CREATE TABLE mytable(
       LinN    VARCHAR(24) NOT NULL PRIMARY KEY
      ,RecType  VARCHAR(2) 
      ,Value   VARCHAR(7)
    );
    INSERT INTO mytable(LinN,RecType,Value) VALUES
     ('1','01','A12345')
    ,('2','02',NULL)
    ,('3','03',NULL)
    ,('4','05',NULL)
    ,('6','01','B67890')
    ,('7','02',NULL)
    ,('8','09',NULL);
    
     ;with mycte as (
    SELECT LinN,RecType,Value,
    CAST(SUBSTRING(MAX( CAST(LinN AS BINARY(4)) + CAST(Value AS BINARY(8)) )
    OVER( ORDER BY LinN ASC ROWS UNBOUNDED PRECEDING ),5,8) AS  VARCHAR(7) ) lastNonNullval 
    FROM mytable 
    ) 
     Update mycte  
     set Value=lastNonNullval;
     
     
     Select * from mytable
    
     drop table mytable

    • Proposed as answer by Naomi N Thursday, April 30, 2020 7:44 PM
    Wednesday, April 29, 2020 1:44 PM
  • CREATE TABLE mytable(
       LinN    VARCHAR(24) NOT NULL PRIMARY KEY
      ,RecType  VARCHAR(2) 
      ,Value   VARCHAR(7)
    );
    INSERT INTO mytable(LinN,RecType,Value) VALUES
     ('1','01','A12345')
    ,('2','02',NULL)
    ,('3','03',NULL)
    ,('4','05',NULL)
    ,('6','01','B67890')
    ,('7','02',NULL)
    ,('8','09',NULL);
    
     ;with mycte as (
    SELECT LinN,RecType,Value
    ,sum(case when RecType='01' then 1 else 0 end) OVER(ORDER BY LinN) 
    grp
    
    FROM mytable 
    ) 
    ,mycte2 as (
     SELECT LinN,RecType,m1.Value, d.value as valueNew  
     FROM  mycte  m1 
     cross apply (select value from mycte m2 
     where m1.Linn >=m2.LinN and m1.grp=m2.grp 
     and value is not null) d
     )
     Update mycte2
     set Value=valueNew; 
     
     Select * from mytable
    
     drop table mytable

    Wednesday, April 29, 2020 2:38 PM
  • Hi , 
    Please try following script  .

    CREATE TABLE mytable(
       LinN    VARCHAR(24) NOT NULL PRIMARY KEY
      ,RecType  VARCHAR(2) 
      ,Value   VARCHAR(7)
    );
    INSERT INTO mytable(LinN,RecType,Value) VALUES
     ('1','01','A12345')
    ,('2','02',NULL)
    ,('3','03',NULL)
    ,('4','05',NULL)
    ,('6','01','B67890')
    ,('7','02',NULL)
    ,('8','09',NULL);
    
    ;with cte as (
    select * , sum(case when [Value] is null then 0 else 1 end )over(order by LinN) ct
    from mytable)
    select LinN,RecType,max([Value])over(partition by ct)[Value]
    from cte
    /*
    LinN                     RecType Value
    ------------------------ ------- -------
    1                        01      A12345
    2                        02      A12345
    3                        03      A12345
    4                        05      A12345
    6                        01      B67890
    7                        02      B67890
    8                        09      B67890*/
     

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by LIONBAZ1 Thursday, April 30, 2020 4:37 PM
    • Marked as answer by Naomi N Thursday, April 30, 2020 7:46 PM
    Thursday, April 30, 2020 3:18 AM
  • Thank you every body for your codes/scripts!

    Special thanks to Rachel ! Your script works perfectly for me!!!!


    • Edited by LIONBAZ1 Thursday, April 30, 2020 4:36 PM
    Thursday, April 30, 2020 4:35 PM
  • You are using update in your question. It seems you don't know what that means.
    Thursday, April 30, 2020 5:49 PM
  • Why did you fail to post it DDL? Why do you think those ASCII pictures you posted are useful to us? We don't even know the name of this table! We have to guess everything and do your job for you.

    Guess at your posting, it implies that you don't know what a table is, and that you think the rows of a table (rows are nothing whatsoever like records!) are in some kind of sequential order. I hope this is for a class and that you're not actually hurting somebody who needs a database designer. 

    No competent RDBMS programmer would use an attribute with a name like "line_nbr" for a table. We do not do physical modeling in SQL. What you're doing is kludging your incompetence in DML.  Here is the best I can do. 

    >> RecType '01' means new group of records starts. <<

    First of all, rows are nothing whatsoever like records. Your assuming there some kind of ordering, which is flagged by a non-null value. This is sequential processing, and has nothing whatsoever to do with a valid relational design. Please go back to your first week or two of classes in database. 

    You might also remember that by definition in RDBMS, all relationships are modeled by scalar values in the columns of rows of a table. Where is the attribute for being in a particular group? You're still doing sequential processing and haven't learned a thing about set oriented programming!  Why don't you clean your data before you put it in your database? 

    CREATE TABLE Foobar
    (foobar_grp CHAR (6) NOT NULL
     CHECK(foobar_grp LIKE'[A-Z][0-9][0-9][0-9][0-9][0-9]'),
     element_name CHAR(2) NOT NULL
    PRIMARY KEY (foobar_grp, element_name)
    );



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 30, 2020 7:30 PM
  • Looks like you had to create a new account, so I marked Rachel's answer as the answer to your post. Your original post was formulated the way it led to think you needed an UPDATE command and not a SELECT, that's why two of the other people suggested UPDATE in their responses.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 30, 2020 7:48 PM