Home » SQL & PL/SQL » SQL & PL/SQL » An update statement required
An update statement required [message #302861] Wed, 27 February 2008 03:38 Go to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi All,


I want an update statement to update the records below

I have a table test:

new_id			id 		PARENT_ID
1			100		
2			102		100
3			103		100
4			104		102
5                       105             102


i need an update statement so that new_id will get updated to parent_id as shown below.

AFTER UPDATE I WANT THE OUTPUT AS BELOW:

new_id			id 		PARENT_ID
1			100		
2			102		1
3			103		1
4			104		2
5                       105             2


Thanks,
Srinivas

[Mod-edit: corrected code-tags.]

[Updated on: Wed, 27 February 2008 03:45] by Moderator

Report message to a moderator

Re: An update statement required [message #302863 is a reply to message #302861] Wed, 27 February 2008 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try?
Why it does not work?
Where are you stuck?

Also post your Oracle version with at least 3 decimals.

Regards
Michel
Re: An update statement required [message #302869 is a reply to message #302861] Wed, 27 February 2008 03:46 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member

I used self join and tried but not able to get the correct output.I will send the update query.
my oracle version is 10.2.0
Re: An update statement required [message #302873 is a reply to message #302861] Wed, 27 February 2008 03:50 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member


Quote:
I will send the update query.



when?

regards,
Re: An update statement required [message #302874 is a reply to message #302861] Wed, 27 February 2008 04:02 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
NOW...............


UPDATE TEST_ID SET PARENT_ID = (SELECT NEW_ID FROM TEST_ID S WHERE S.ID = TEST_ID.PARENT_ID);


some times it fails to update query ,and it has been very difficult to find where it failed in lakh records.
Re: An update statement required [message #302880 is a reply to message #302874] Wed, 27 February 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It will be even more difficult to us to find where it fails if you don't post us where it fails.
Why do you think it fails?

Regards
Michel
Re: An update statement required [message #302884 is a reply to message #302861] Wed, 27 February 2008 04:27 Go to previous message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
According to you:
Quote:
it has been very difficult to find where it failed in lakh records


but also,
Quote:
some times it fails to update query



May I have the row in which you think so ?

regards,
Previous Topic: To find the Unique Indexes
Next Topic: Bulk insert
Goto Forum:
  


Current Time: Mon Dec 05 13:01:30 CST 2016

Total time taken to generate the page: 0.11028 seconds