Home » SQL & PL/SQL » SQL & PL/SQL » how to delete incorrect data within a table and change it to the correct one.Please help me.
icon2.gif  how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218670] Fri, 09 February 2007 04:40 Go to next message
suzane
Messages: 6
Registered: February 2007
Location: Mumbai
Junior Member
Hi,

I have created a table 'EMP' by inserting certain values to each row. But, later on I discovered that few entries i made were wrong. Eg: Instead of putting hire_date '01-01-1980' i have put '01-11-1980'.

How to go about it...!?

Your earliest answer is appreciated.

Thanks & Regards,
Suzane

Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218675 is a reply to message #218670] Fri, 09 February 2007 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just use a simple Update statement.
Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218676 is a reply to message #218670] Fri, 09 February 2007 04:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
update emp
set hire_date = to_date('01-11-1980','dd-mm-yyyy')
where hire_date = to_date('01-01-1980','dd-mm-yyyy')
/
Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218677 is a reply to message #218676] Fri, 09 February 2007 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That statement is only correct if you want to change EVERY occurrence of one value to the other one. I'm not certain that's whay the OP is looking for.
Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218680 is a reply to message #218677] Fri, 09 February 2007 05:01 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:

That statement is only correct if you want to change EVERY occurrence of one value to the other one. I'm not certain that's whay the OP is looking for.



yes I agree..... the where condition should be different and properly identified if OP wants to change only some of the occurrance.

[Updated on: Fri, 09 February 2007 05:02]

Report message to a moderator

icon7.gif  Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218684 is a reply to message #218680] Fri, 09 February 2007 05:18 Go to previous messageGo to next message
suzane
Messages: 6
Registered: February 2007
Location: Mumbai
Junior Member
Hey... Thanks for the reply,

I tried UPDATE statement. It worked for single entry in a cloumn. BUT, what if all the entries are incorrect or not in order....!!? Then what shud be done to that column....?

Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218707 is a reply to message #218684] Fri, 09 February 2007 07:41 Go to previous messageGo to next message
meistre
Messages: 4
Registered: February 2007
Junior Member
Hi!

You still have the other table from which you created the EMP one, right?

If so, you could perform un update like this:

Update EMP a set a.hire_date = ( select b.hire_date
from original_table b
where a.empid = b.empid )

You must be sure that selecting records by empid from the "original_table" will only return one record for one empid. If there is more than one record, you must filter the query further.
Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218709 is a reply to message #218707] Fri, 09 February 2007 07:43 Go to previous messageGo to next message
meistre
Messages: 4
Registered: February 2007
Junior Member

And as for deleting, you could do something like this


delete from EMP a
where not exists ( select 1 from other_table b
where a.empid = b.empid )
Re: how to delete incorrect data within a table and change it to the correct one.Please help me. [message #218712 is a reply to message #218684] Fri, 09 February 2007 09:14 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
suzane wrote on Fri, 09 February 2007 12:18
BUT, what if all the entries are incorrect or not in order....!!?

Not in order?
You don't expect the ROWS to be in a specific order, do you? There is no such thing as a natural order in a relational database.
Previous Topic: Do joins really improve query performance?
Next Topic: ORA-00918 on UNIONS
Goto Forum:
  


Current Time: Mon Dec 05 09:11:48 CST 2016

Total time taken to generate the page: 0.06175 seconds