Home » SQL & PL/SQL » SQL & PL/SQL » To get the old value in an update statement
To get the old value in an update statement [message #267998] Mon, 17 September 2007 01:59 Go to next message
tosanu
Messages: 7
Registered: December 2006
Location: INDIA
Junior Member
Hi,

While updating a table, is it possible to get the old value of a column. I need to get it inside a PL\SQL block and not in a trigger.

for example :-

DECLARE
l_amount NUMBER(5) := 0;
BEGIN
UPDATE CustTransactions
SET amount = 150
WHERE cust_id = 5412
RETURNING amount INTO l_amount;

DBMS_OUTPUT.PUT_LINE ( 'l_amount= ' || l_amount );
END;

By using the RETURNING clause i am getting the new updated value. I need the old value for some processing. Do i have to explicitly query it before the update stmt? Pls reply.

Thanks & Regards,

Sanu
Re: To get the old value in an update statement [message #267999 is a reply to message #267998] Mon, 17 September 2007 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Execute a SELECT FOR UPDATE before the UPDATE.

Regards
Michel
Re: To get the old value in an update statement [message #268001 is a reply to message #267999] Mon, 17 September 2007 02:08 Go to previous messageGo to next message
tosanu
Messages: 7
Registered: December 2006
Location: INDIA
Junior Member

Thanks Michel...

But in that case, will it go for a seperate query execution before actual update?
Re: To get the old value in an update statement [message #268021 is a reply to message #268001] Mon, 17 September 2007 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes and no.
If you use "WHERE CURRENT OF" clause in UPDATE you only execute once the SELECT part of the UPDATE. You can see it at an update in 2 parts/statements.

Regards
Michel
Re: To get the old value in an update statement [message #268028 is a reply to message #268021] Mon, 17 September 2007 03:59 Go to previous messageGo to next message
tosanu
Messages: 7
Registered: December 2006
Location: INDIA
Junior Member

thank you...

Re: To get the old value in an update statement [message #466107 is a reply to message #267998] Sat, 17 July 2010 00:32 Go to previous messageGo to next message
developer961
Messages: 1
Registered: July 2010
Junior Member
getting old value, please test it

update table set column1=col_newValue output deleted.*, Inserted.*
where column2=column2_whereCondition

deleted.* show the all columns of the old row
inserted.* show the all columns of the new row(row was updated)

you can use : inserted.Column1 or deleted.Column1


you can use output command in insert and delete t-sql commands
Shocked

[Mod-edit: Frank removed unrelated provocative remark + replies referring to it]

[Updated on: Sat, 17 July 2010 01:01] by Moderator

Report message to a moderator

Re: To get the old value in an update statement [message #466115 is a reply to message #466107] Sat, 17 July 2010 01:15 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is an Oracle forum, what you say is irrelevant.

Please read OraFAQ Forum Guide.

Regards
Michel
Previous Topic: How to delete the records of accidental commit
Next Topic: REPLACE ON USER_MVIEWS
Goto Forum:
  


Current Time: Thu Feb 13 03:21:45 CST 2025