Update upper row data with next row data that was detached partially from import.
Update upper row data with next row data that was detached partially from import.
(OP)
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. See attachment for Data table.
Thanks
Thanks
RE: Update upper row data with next row data that was detached partially from import.
Suppose I would have a table called Fredong and this select
CODE
delivers this result
CODE
To correct the data, I would try something like this:
CODE
CODE
RE: Update upper row data with next row data that was detached partially from import.
Thanks for your response. The strip and length functions are from python and I am using TSQL. I replaced it with LTRIM & TRIM and LEN. It did not resolved my problem but getting the same result. Any ideas?
select
t1.id as id,
t1.DocName as DocName,
case
when len(LTRIM(RTRIM(t1.col1))) = 1 then ''
else t1.col1
end as col1,
t1.col2 as col2,
case
when len(LTRIM(RTRIM(t2.col1))) = 1 then
concat(LTRIM(RTRIM(t1.col3)), t2.col1)
else t1.col3
end as col3
from
fredong t1 left join
fredong t2 on t1.DocName = t2.DocName and
t1.id != t2.id
RE: Update upper row data with next row data that was detached partially from import.
I tried the select I posted on DB2, the functions STRIP and LENGTH are available - see here:
https://www.ibm.com/support/knowledgecenter/SSEPEK...
https://www.ibm.com/support/knowledgecenter/en/SSE...
Unfortunately I don't have MS SQL.
Does the SELECT not work for you, or does it give the wrong result ?
RE: Update upper row data with next row data that was detached partially from import.
The code did not return any error but the result is still the same where I am before. See my comments below --
select
t1.id as id,
t1.DocName as DocName,
case
when len(LTRIM(RTRIM(t1.col1))) = 1 then '' -- Length varies so it can be more than 1. I did > 1 but it empty my col1
else t1.col1
end as col1,
t1.col2 as col2,
case
when len(LTRIM(RTRIM(t2.col1))) = 1 then -- Length varies so it can be more than 1. I did > 1 but it empty my col1
concat(LTRIM(RTRIM(t1.col3)), t2.col1)
else t1.col3
end as col3
from
fredong t1 left join
fredong t2 on t1.DocName = t2.DocName and
t1.id != t2.id
RE: Update upper row data with next row data that was detached partially from import.
You gave only an example where only the last characters are separated into the next line, so I thought only about length of 1.
If all your string data in col3 should have have the some length N, then try to modify the conditions to
CODE
RE: Update upper row data with next row data that was detached partially from import.
CODE