Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE is really a DELETE followed by an INSERT (Oracle 9.2.0.1, Oracle version N/A))
UPDATE is really a DELETE followed by an INSERT [message #377007] Fri, 19 December 2008 07:51 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member
UPDATE is really a DELETE followed by an INSERT --Can anyone demonstrate with an example. Thanks in advance
Re: UPDATE is really a DELETE followed by an INSERT [message #377008 is a reply to message #377007] Fri, 19 December 2008 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No as this is wrong.

Regards
Michel
Re: UPDATE is really a DELETE followed by an INSERT [message #377011 is a reply to message #377008] Fri, 19 December 2008 08:03 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
As Michel said, an UPDATE is not the same as a DELETE followed by an INSERT. Rows are updated in-place (i.e. rowid is retained). INSERTed rows might go into different blocks and will have different rowids. There are other possible side effects as well, think about performance, triggers that will fire, etc.
Re: UPDATE is really a DELETE followed by an INSERT [message #377012 is a reply to message #377008] Fri, 19 December 2008 08:07 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I have found this statement in one of the Book written by Tom Kyte's. Is it false then? Then whats the truth? Can you please demonstrate with an example ..It would help
Re: UPDATE is really a DELETE followed by an INSERT [message #377014 is a reply to message #377007] Fri, 19 December 2008 08:09 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Where did you hear such a silly thing as this? Pure nonsense. Or is this a question asked by your teacher that you did not know the answer for and decided to trick us into giving you an answer?

One example to disprove this is if you had a foreign key constraint on a table then tried to update a non-key column in the parent record. What do you think would happen? This exercise is left to the user.

How about a table for 4 billion rows of 50 columns. Do you think Oracle would be delete 4 billion rows an reinsert them if you needed to update a single column from Y to N? Maybe Microsoft would do that.

I am now going to look at some of your previous posts to see what other delusions you may have.
Re: UPDATE is really a DELETE followed by an INSERT [message #377015 is a reply to message #377011] Fri, 19 December 2008 08:10 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
I have found the statement in Expert one on One by Thomas Kyte. Its in page number 134 (Locking and Concurrency)
Re: UPDATE is really a DELETE followed by an INSERT [message #377017 is a reply to message #377015] Fri, 19 December 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you misread and misunderstood it.

Regards
Michel
Re: UPDATE is really a DELETE followed by an INSERT [message #377020 is a reply to message #377015] Fri, 19 December 2008 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the book I found page 134 is... blank.
So post exact section name (not just chapter name).

Regards
Michel
Re: UPDATE is really a DELETE followed by an INSERT [message #377028 is a reply to message #377015] Fri, 19 December 2008 09:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
That section of the book is in regards to Sybase UPDATE functionality.
Re: UPDATE is really a DELETE followed by an INSERT [message #377273 is a reply to message #377028] Mon, 22 December 2008 03:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you have a page number or section - I'd like to read details of that (and am too lazy to re-read the whole chapter)
Re: UPDATE is really a DELETE followed by an INSERT [message #377305 is a reply to message #377273] Mon, 22 December 2008 05:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There were two publishers of the book so the page number may be different, I found it on page 134 in the Apress version.
Re: UPDATE is really a DELETE followed by an INSERT [message #377308 is a reply to message #377007] Mon, 22 December 2008 05:20 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
This is for Sybase.

[Edit MC: Copyrighted content removed. Unless you have agreement from T. Kyte and his editor you must NOT post part of his book. A couple of lines is allowed, a paragraph is too much.]

Thanks
Trivendra

[Updated on: Mon, 22 December 2008 05:45] by Moderator

Report message to a moderator

Previous Topic: Exception handling
Next Topic: help in the count
Goto Forum:
  


Current Time: Sat Dec 10 14:39:13 CST 2016

Total time taken to generate the page: 0.08265 seconds