Home » SQL & PL/SQL » SQL & PL/SQL » Single update to get one column value using another column value (Oracle 12c)
Single update to get one column value using another column value [message #679346] Tue, 25 February 2020 04:04 Go to next message
sss111ind
Messages: 613
Registered: April 2012
Location: India
Senior Member


Hi All,

I want to get the new value of comm using new value of sal ,however it's not happening using single update. Please help.

existing value:--SAL:1610	COMM:300
excepted value:--SAL:1620(COMING)	COMM:1920 (NOT COMING)



UPDATE emp
SET
    comm = comm + sal,
    sal = sal + 10
WHERE
    empno = '7499';

Re: Single update to get one column value using another column value [message #679347 is a reply to message #679346] Tue, 25 February 2020 04:09 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
Er... Am I missing something?
UPDATE emp
SET
    comm = comm + sal + 10,
    sal = sal + 10
WHERE
    empno = '7499';
Re: Single update to get one column value using another column value [message #679348 is a reply to message #679347] Tue, 25 February 2020 04:18 Go to previous messageGo to next message
sss111ind
Messages: 613
Registered: April 2012
Location: India
Senior Member

Thanks for reply,

Actually in place of sal+10 , I am using a function and there is 3 function call. And instead of update we are using merge statement. So I want to call the function one time for each row. Is there any way to achieve this.

Re: Single update to get one column value using another column value [message #679349 is a reply to message #679348] Tue, 25 February 2020 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then I suggest you show us the actual code you're actually running.
Hard to say what the problem is without it.
Re: Single update to get one column value using another column value [message #679350 is a reply to message #679349] Tue, 25 February 2020 05:05 Go to previous messageGo to next message
sss111ind
Messages: 613
Registered: April 2012
Location: India
Senior Member

Please find the dummy code.The update and merge are used 2 place separately and both are necessary.


CREATE OR REPLACE FUNCTION BONUS_PERCENT(SAL IN NUMBER) RETURN VARCHAR2 AS 
BEGIN
  RETURN SAL + 10;
END BONUS_PERCENT;
/
UPDATE emp
SET
    sal = bonus_percent(sal),
    comm = comm + sal --calculation should happen based on new sal
WHERE
    empno = '7499';

MERGE INTO emp tgt
USING (
          SELECT
              *
          FROM
              emp
      )
src ON ( tgt.empno = src.empno )
WHEN MATCHED THEN UPDATE
SET sal = bonus_percent(sal),
    comm = comm + bonus_percent(sal);--calculation should happen based on new sal

Re: Single update to get one column value using another column value [message #679351 is a reply to message #679350] Tue, 25 February 2020 05:18 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
To follow John's example the update should be:
UPDATE emp
SET
    sal = bonus_percent(sal),
    comm = comm + bonus_percent(sal)
WHERE
    empno = '7499';
The merge looks ok as it is.
Re: Single update to get one column value using another column value [message #679352 is a reply to message #679346] Tue, 25 February 2020 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

UPDATE updates from the old values not the new ones, this is the standard, and hopefully otherwise it won't be deterministic.

Re: Single update to get one column value using another column value [message #679353 is a reply to message #679352] Tue, 25 February 2020 05:30 Go to previous messageGo to next message
vippysharma
Messages: 61
Registered: May 2013
Location: www
Member
i think OP said...
Quote:
So I want to call the function one time for each row.
and below code is calling function two time for each row...
UPDATE emp
SET
    sal = bonus_percent(sal),
    comm = comm + bonus_percent(sal)
WHERE
    empno = '7499';
Re: Single update to get one column value using another column value [message #679354 is a reply to message #679353] Tue, 25 February 2020 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
May work as an update join:
UPDATE (SELECT e.sal AS old_sal, 
               e2.bonus_percent(sal) AS new_sal,
               e.comm AS comm
        FROM emp e JOIN emp e2 ON e.empno = e2.empno
        WHERE e.empno = 7499
       )
SET
    old_sal = new_sal,
    comm = comm + new_sal;
There's no way to avoid 2 function calls in a standard update.

[Updated on: Tue, 25 February 2020 05:35]

Report message to a moderator

Re: Single update to get one column value using another column value [message #679355 is a reply to message #679354] Tue, 25 February 2020 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Doing it in merge is simple enough, just call the function in the USING section:
MERGE INTO emp tgt
USING (
          SELECT
              empno,
              bonus_percent(sal) AS newsal
          FROM
              emp
      )
src ON ( tgt.empno = src.empno )
WHEN MATCHED THEN UPDATE
SET tgt.sal = src.newsal,
    tgt.comm = tgt.comm + src.newsal;
Re: Single update to get one column value using another column value [message #679356 is a reply to message #679348] Tue, 25 February 2020 05:50 Go to previous messageGo to next message
John Watson
Messages: 8274
Registered: January 2010
Location: Global Village
Senior Member
sss111ind wrote on Tue, 25 February 2020 10:18
Thanks for reply,

Actually in place of sal+10 , I am using a function and there is 3 function call. And instead of update we are using merge statement. So I want to call the function one time for each row. Is there any way to achieve this.

Even if the function is called several times, you can prevent it being executed several times by declaring the RETURN as DETERMINISTIC. Or better still, if you have EE licences you could enable the function result cache which would let one execution be shared across statements and sessions.
Re: Single update to get one column value using another column value [message #679357 is a reply to message #679356] Tue, 25 February 2020 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming it actually is deterministic of course.
OPs example function may well be overly simplified.
Re: Single update to get one column value using another column value [message #679359 is a reply to message #679357] Tue, 25 February 2020 07:44 Go to previous message
sss111ind
Messages: 613
Registered: April 2012
Location: India
Senior Member

Thank you very much to all for your input, I will write a procedure to do this.
Previous Topic: Return defined column size of varchar and varchar2 data types
Next Topic: Get (and lock) first unlocked row?!
Goto Forum:
  


Current Time: Thu Jun 04 04:24:04 CDT 2020