Data Cleansing using SQL

Database
N/A

Data Cleansing using SQL

hi
I have one Table XYZ with 100 rows and four columns(fields).
And if by mistake the second column values are shifted down by one with remaining data undisturbed ,how to get the original table again?

EX:
InItially

COL1..... COL2..... COL3..... COL4

1 .........11...... 111...... 1111
2......... 22...... 222...... 2222
3......... 33...... 333...... 3333
4......... 44...... 444...... 4444

After error

COL1.... COL2.... COL3.... COL4

1......... ..NULL.... .111 .....1111
2....... .....11 ......222 .....2222
3............ 22...... 333 .....3333
4 ............33 ......444 ......4444
NULL.......44 ......NULL..... NULL

How to restore the table to its original one.

6 REPLIES
Teradata Employee

Re: Data Cleansing using SQL

Are the values of Col1 in sequence .... as in .... in order .... 1,2,3,4.... ?
N/A

Re: Data Cleansing using SQL

hi adeel,
no,they are not in order(need not be) i just took them as for example.
N/A

Re: Data Cleansing using SQL

Restore from backup? Unfortunately, I'm not sure there is a good option here. What column(s) make up your primary key of this table? Is there a process that introduces this kind of error? Can you go back to a source system and do a comparison to get the correct PK/Attribute relationships?
N/A

Re: Data Cleansing using SQL

hi mnylin,
yes,restoring is the last option but a costly option too.The first column make up the UPI for the table.This error happened during migration of database and i am not sure about the details as it is out of my scope , I getting hints from my collegues to try out select ..insert into option.
N/A

Re: Data Cleansing using SQL

"shifted down by one" means there *is* a kind of order.

Then it would be easy:
select col1,
min(col2) over (order by whatever rows between 1 following and 1 following)
col3,col4
from tab
qualify col1 is not null

But if there's no order how will you know which was the original value?

Dieter

Re: Data Cleansing using SQL

Data cleansing is the process of uncovering and correcting inconsistent records from a table, a set, or database. This is used mainly in databases to identify imperfect, incorrect, erroneous and irrelevant parts of the data and then modifying, replacing or deleting the incorrect data.

Informatics Outsourcing Provides the service Worldwide.