Update parent row from child row that has partial data.

Question

Update parent row from child row that has partial data.

pcq0125

SSCrazy

Points: 2890
More actions
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

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

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

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 1

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

pcq0125 SSCrazy Points: 2890 More actions · Answer 2

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,

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 3

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!

pcq0125 SSCrazy Points: 2890 More actions · Answer 4

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.

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 5

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

pcq0125 SSCrazy Points: 2890 More actions · Answer 6

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

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 7

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.

pcq0125 SSCrazy Points: 2890 More actions · Answer 8

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

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 9

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://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

pcq0125 SSCrazy Points: 2890 More actions · Answer 10

Jonathan,

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

Attachments:

You must be logged in to view attached files.

pcq0125 SSCrazy Points: 2890 More actions · Answer 11

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.

pcq0125 SSCrazy Points: 2890 More actions · Answer 12

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.

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 13

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

pcq0125 SSCrazy Points: 2890 More actions · Answer 14

pcq0125

SSCrazy

Points: 2890

January 29, 2020 at 4:41 pm

#3721288

Thank you it works.