This content has been marked as final. Show 10 replies
957595 wrote:What is the criteria for doing UPDATE as compared to doing DELETE?
I have an requirement that assigned to me,need assistance from any of you that will be much help full for me to start up.
There are some data discrepancies in some tables, i have to clear out by deleting or updating the correct one.
How do you decide which data is correct & which data is "wrong"?
sushaant wrote:nobody here prevents you from doing as above.
I need redefine integrity constraint with cascade on delete or update option in all the tables. That is the only simplest way. :)
You have our permission to proceed without posting more here.
Status Level: Newbie
Registered: Sep 7, 2012
Total Posts: 6
Total Questions: 5 (4 unresolved)
I extend my condolences to you since you rarely get answers to your questions here.
I have two parent records both resembles the same,so at this time i have to consider one record as correct and other as a duplicate.
update the duplicate with the correct records.
similar like below
Duplicate department id for the same department.
so in this scenario.all the employees are updated with both department,i have to delete the duplicate department's entry.
To delete the duplicate department_id . i need to update all the employee with unique department_id,after updating all the employees i need to delete the department_id.
here the dept_id 1 is updated for most employee..i will consider 1 as unique dept_id and update all the employees dept_id with 1 and
need to delete the dept_id 2.
To update the cuurent table and so on, i have integriy constranints and it's preventes due to the current table child records.
Unfortunately you (a human) are the only one that can determine what data you want to keep and what data you want to delete.
You cannot delete a PARENT row if there are CHILD rowss that point to it using a foreign key constraint. But you can delete any CHILD rows you want. If a child row is the 'parent' for some other tables rows then, yes, you must go delete those children first and so on.
There is no automated way to do this other than the ON DELETE CASCADE setting for the parent tables.
So the problem is usually one of doing things in the correct order. Create the new parent row first. Then change each of the 'bad' parents child rows to point to the new parent row. Then you can delete the 'bad' parent row since it no longer has any children.