Home » SQL & PL/SQL » SQL & PL/SQL » can we do it with one update statement for updating the same column with different conditions (oracle 10g)
can we do it with one update statement for updating the same column with different conditions [message #443134] Fri, 12 February 2010 23:30 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
currently i am using two update statements for updating same column with different conditions.

can we do it with one update statement?is it possible?


 
 
 UPDATE GT_TEST TEST
       set spt.paymentmode = (SELECT rcd.paymentmode
                                     FROM classdtl rcd
                                    WHERE rcd.master = TEST.docid)
     WHERE TEST.MODE = 1
       AND TEST.INDICATOR = 'PRT';

  
    UPDATE GT_TEST TEST
       set spt.paymentmode = (SELECT hcg.paymentmode
                                     FROM charges hcg
                                    WHERE hcg.docid = TEST.docid)
     WHERE TEST.MODE = 2
       AND TEST.INDICATOR = 'PRT';
Re: can we do it with one update statement for updating the same column with different conditions [message #443139 is a reply to message #443134] Sat, 13 February 2010 00:06 Go to previous messageGo to next message
chanjalj
Messages: 1
Registered: February 2010
Location: Bangalore
Junior Member
Give this a try...

UPDATE GT_TEST TEST
set spt.paymentmode = CASE WHEN TEST.MODE = 1 THEN
(SELECT rcd.paymentmode
FROM classdtl rcd
WHERE rcd.master = TEST.docid)
WHEN TEST.MODE = 2 THEN
(SELECT hcg.paymentmode
FROM charges hcg
WHERE hcg.docid = TEST.docid)
ELSE spt.paymentmode
END
WHERE TEST.INDICATOR = 'PRT';
Re: can we do it with one update statement for updating the same column with different conditions [message #443160 is a reply to message #443134] Sat, 13 February 2010 01:31 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UPDATE GT_TEST TEST
       set paymentmode = 
              decode(test.mode,
                     1,
                             (SELECT rcd.paymentmode
                                     FROM classdtl rcd
                                    WHERE rcd.master = TEST.docid),
                     2,
                             (SELECT hcg.paymentmode
                                     FROM charges hcg
                                    WHERE hcg.docid = TEST.docid)
                     )
     WHERE TEST.MODE in (1, 2)
       AND TEST.INDICATOR = 'PRT';

Regards
Michel

[Updated on: Sat, 13 February 2010 01:31]

Report message to a moderator

Previous Topic: Summing of time
Next Topic: Sql query data to filter in the week & Monthly wise
Goto Forum:
  


Current Time: Mon Dec 05 02:54:23 CST 2016

Total time taken to generate the page: 0.11248 seconds