Post reply

Update parent row from child row that has partial data.

  • January 28, 2020 at 10:13 pm

    #3721039

    On the same table I have table rows that are broken from the import and went to the next row. I need to bring it back to the parent row. For example ID 1  Col3  supposed to have the value 'GHI' but the 'I' went to the 2nd row or ID 2 Col1 cell. Same applies to ID3 supposed to have 'GGG'  but it went to the next row 4 or ID 4.  Also, note that not every same filename has 2 rows some files have 1 row only but did not pose a problem. Any SQL statements of advise? This is MSSQL 2017.

    Thanks

  • January 28, 2020 at 11:58 pm

    #3721059

    You could try using LEAD

    SELECT T.Id, T.DocName, T.col1, T.col2, 
           T.col3 +ISNULL(LEAD(col1) OVER (PARTITION BY T.DocName ORDER BY T.Id),'') col3
      FROM (VALUES (1,'File1','ABC','DEF','GH'),
                   (2,'File1','I','JKH','LMN'),
                   (3,'File2','DDD','RRR','GG'),
                   (4,'File2','G','YYY','PPP'),
                   (5,'File3','TTT','CCC','MMM'),
                   (6,'File4','WWW','QQQ','ZZZ')) T(Id, DocName, col1, col2, col3)

    Capture

  • January 29, 2020 at 3:51 am

    #3721085

    Hi Jonathan,

    Thanks for your input but your code will not work for me. This is because the I have thousand of files that I am importing. Thus hard coded the file name will be tedious. I am looking for SQL code that can work with numerous file. Let me know. Thanks,

  • January 29, 2020 at 9:33 am

    #3721118

    pcq0125 wrote:

    Hi Jonathan,

    Thanks for your input but your code will not work for me. This is because the I have thousand of files that I am importing. Thus hard coded the file name will be tedious. I am looking for SQL code that can work with numerous file. Let me know. Thanks,

    That was just simulating the data in your table. You didn't supply any consumable data so instead of selecting from a table I select from values. You don't need to hard code anything!

  • January 29, 2020 at 1:27 pm

    #3721177

    Jonathan,

    You code works for hardcoded values. I replaced the Values with my #Temp1 table. I am getting an error. Any ideas?

     

    SELECT T.Id, T.DocName, T.col1, T.col2,

    T.col3 +ISNULL(LEAD(col1) OVER (PARTITION BY T.DocName ORDER BY T.Id),'') col3

    FROM  #Temp1  T(Id, DocName, col1, col2, col3)

    Msg 321, Level 15, State 1, Line 88

    "ID" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

     

  • January 29, 2020 at 1:41 pm

    #3721202

    pcq0125 wrote:

    Jonathan,

    You code works for hardcoded values. I replaced the Values with my #Temp1 table. I am getting an error. Any ideas?

    SELECT T.Id, T.DocName, T.col1, T.col2,

    T.col3 +ISNULL(LEAD(col1) OVER (PARTITION BY T.DocName ORDER BY T.Id),'') col3

    FROM  #Temp1  T(Id, DocName, col1, col2, col3)

    Msg 321, Level 15, State 1, Line 88

    "ID" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90. 

    The problem is you have the column names from the inline values table at the end of your query, just remove them:

    SELECT T.Id,
           T.DocName,
           T.col1,
           T.col2,
           T.col3 + ISNULL(LEAD(col1) OVER(PARTITION BY T.DocName ORDER BY T.Id), '') col3
      FROM #Temp1 T

  • January 29, 2020 at 1:54 pm

    #3721207

    Plucked in your new code. I am still getting the same result.

  • January 29, 2020 at 2:18 pm

    #3721222

    pcq0125 wrote:

    Plucked in your new code. I am still getting the same result.

    Which result? This?

    Msg 321, Level 15, State 1, Line 88
    "ID" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

  • January 29, 2020 at 2:24 pm

    #3721224

    I meant your code did not return any errors but the output result is the same where I am before below

     

  • January 29, 2020 at 2:28 pm

    #3721227

    Your data must be different from the data I provided.

    If you want any further help you will have to supply some consumable data.

    https://qa.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • January 29, 2020 at 2:53 pm

    #3721229

    Jonathan,

    Please see my data below. Also in the attachment with pipe delimited.

    Attachments:
    You must be logged in to view attached files.

  • January 29, 2020 at 3:15 pm

    #3721237

    Jonathan,

    Actually your code works. I did not mention there is another parent row and when I exclude the parent row it works. Thanks so much for your help.

  • January 29, 2020 at 3:33 pm

    #3721249

    last question. I tried to make it an update statement but got an error below. Any ideas how to fix this?

    Thanks

    Update T = T.col3 + ISNULL(LEAD(col1) OVER(PARTITION BY T.DocName ORDER BY T.Id), '')

    FROM #Temp1 T  Where T.col1 <>'Grandparent header row'

    Msg 4108, Level 15, State 1, Line 87

    Windowed functions can only appear in the SELECT or ORDER BY clauses.

     

     

  • January 29, 2020 at 3:42 pm

    #3721264

    I would put the query in a CTE and update the CTE:

    ;WITH CTE AS
    (
        SELECT T.col3 col3,
               T.col3 + ISNULL(LEAD(col1) OVER(PARTITION BY T.DocName ORDER BY T.Id), '') Newcol3
          FROM #Temp1 T
    )
    UPDATE CTE
       SET CTE.Col3 = CTE.Newcol3
     WHERE CTE.Col3 <> CTE.Newcol3

  • January 29, 2020 at 4:41 pm

    #3721288

    Thank you it works.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply