To get the old value in an update statement [message #267998] |
Mon, 17 September 2007 01:59  |
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 #466107 is a reply to message #267998] |
Sat, 17 July 2010 00:32   |
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
[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
|
|
|
|