Home » SQL & PL/SQL » SQL & PL/SQL » update records (Oracle 9.2.0.1.0 , Windows XP)
update records [message #308552] Mon, 24 March 2008 16:22 Go to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Hello friends,

I am looking help for the following to achieve desired resutls.

Please find here is the demo_tab.


DROP TABLE DEMO_TAB

CREATE TABLE DEMO_TAB
(
  SEQCLAIMID    NUMBER(9),
  LINE_NUMBER   NUMBER,
  SYS_REV_CODE  VARCHAR2(8 BYTE),
  REVCODE       VARCHAR2(48 BYTE),
  QUANTITY1     NUMBER(15,1),
  PROC_CODE     VARCHAR2(8 BYTE),
  CLMSTAT       CHAR(1 BYTE),
  PROCSTAT      CHAR(1 BYTE),
  PAID          NUMBER(18,2)
);


INSERT ALL 
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, REVCODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT)
 Values
   (245976246, 1, '0124', 1, '124', 'I', 'F')
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, REVCODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT)
 Values
   (245976246, 2, '0124', 1, '124', 'I', 'F')
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, REVCODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT)
 Values
   (245976246, 3, '0124', 1, '124', 'I', 'F')
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, REVCODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT)
 Values
   (245976246, 4, '0124', 1, '124', 'I', 'F')
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, REVCODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT)
 Values
   (245976246, 5, '0124', 1, '124', 'I', 'F')
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, SYS_REV_CODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT, PAID)
 Values
   (245976246, 6, '124', 13, 'SYSPSYC2', 'P', 'P', 6500)
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, SYS_REV_CODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT, PAID)
 Values
   (245976246, 7, ' ', 1, '--', 'P', 'P', 100)
into DEMO_TAB
   (SEQCLAIMID, LINE_NUMBER, SYS_REV_CODE, QUANTITY1, PROC_CODE, CLMSTAT, PROCSTAT, PAID)
 Values
   (245976246, 8, ' ', 1, '--', 'P', 'P', 60)
SELECT * FROM DUAL;
COMMIT;




SELECT * FROM DEMO_TAB;

SEQCLAIMID LINE_NUMBER SYS_REV_ REVCODE            QUANTITY1 PROC_COD C P       PAID
---------- ----------- -------- ----------------- ---------- -------- - - ----------
 245976246           1          0124                       1 124      I F
 245976246           2          0124                       1 124      I F
 245976246           3          0124                       1 124      I F
 245976246           4          0124                       1 124      I F
 245976246           5          0124                       1 124      I F
 245976246           6 124                                13 SYSPSYC2 P P       6500
 245976246           7                                     1 --       P P        100
 245976246           8                                     1 --       P P         60
6 rows selected.


DESIRED RESULT IS :

SEQCLAIMID LINE_NUMBER SYS_REV_ REVCODE          QUANTITY1 PROC_COD C P       PAID
---------- ----------- -------- --------------- ---------- -------- - - ----------
 245976246           1  124     0124                    16 124      P F	     6760



So quantity of line_number 1 will be plus of line_number 6,7,8
and sum all the paid and clmstat,sys_rev_code updated accordingly.


I am able to update sys_rev_code and clmstat
with the following update statement.

UPDATE demo_tab t
SET (t.sys_rev_code,t.clmstat)
= (SELECT sys_rev_code,clmstat FROM demo_tab where proc_code like 'SYS%')
WHERE LINE_NUMBER = 1


how to added up Quantity and Paid and delete the rest of the rows ?

Really appreciate your help.
Re: update records [message #308558 is a reply to message #308552] Mon, 24 March 2008 20:43 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It might be easier and faster to rebuild the table
Previous Topic: Break the Lines from a table and insert into new table
Next Topic: String function
Goto Forum:
  


Current Time: Mon Dec 05 15:13:42 CST 2016

Total time taken to generate the page: 0.12336 seconds