Home » SQL & PL/SQL » SQL & PL/SQL » update statement help (oracle 11g)
update statement help [message #574673] Mon, 14 January 2013 15:55 Go to next message
swas_recall
Messages: 7
Registered: May 2012
Location: Bangalore
Junior Member

Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM37034992',37034992,'WM','0001','IMUK12345',null,10);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM37034992',37034992,'WM','0001','IMUK12346',null,30);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM34962678',34962678,'WM','0001','BXSS77834',null,12);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM34962678',34962678,'WM','0001','BXSS876542',null,10);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM34962678',34962678,'WM','0001','BXSS77835',null,8);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM38396668',38396668,'WM','0001','HXSS77856',null,20);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM39100299',39100299,'WM','0001','ZXNE77834',null,20);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSSW38485041',38485041,'SW','0001','NXSS77939',null,20);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSSM38710752',38710752,'SM','0001','EQUK13579',null,30);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSSM38710752',38710752,'SM','0001','EQUK13580',null,10);
Insert into "fld_map_isdn_bearer_24" (RUN_ID,BILL_ACCNT_KEY,BILLING_ACCOUNT_NO,DISTRICT_CODE,INST_SEQUENCE_NO,FM_BEARERID,FM_BEARERSEQNO,FM_BEARERCHNLS) values (909,'CSSWM38199380',38199380,'WM','0001','IMSS120000',null,30);



Below is the sql i am using to generate the FM_BEARERSEQNO.I need to then update the column based on the sequence values.However my update statement is returning multiple rows.

How can i update this column

Select statement result is below



SELECT district_code,
  billing_account_no,
  inst_sequence_no,
  FM_bearerid,
  lpad(dense_rank() over (partition BY district_code
  ||billing_account_no
  ||inst_sequence_no order by FM_bearerid),2,0) FM_Bearerseqno
FROM fld_map_isdn_bearer_24
WHERE 'CSS'
  ||district_code
  ||billing_account_no IN ( 'CSSWM38199380', 'CSSWM34962678', 'CSSSM38710752', 'CSSWM37034992', 'CSSSW38485041', 'CSSWM38396668')
       order by district_code,billing_account_no,FM_Bearerseqno;



Result :

SM	38710752	0001	EQUK13579	01
SM	38710752	0001	EQUK13580	02
SW	38485041	0001	NXSS77939	01
WM	34962678	0001	BXSS77834	01
WM	34962678	0001	BXSS77835	02
WM	34962678	0001	BXSS876542	03
WM	37034992	0001	IMUK12345	01
WM	37034992	0001	IMUK12346	02
WM	38199380	0001	IMSS120000	01
WM	38396668	0001	HXSS77856	01



Update statement i was trying was as below but i am not finding a clue...


   
UPDATE fld_map_isdn_bearer_24 fm
SET
  (
    fm_bearerseqno =
  SELECT
    -- district_code,billing_account_no,inst_sequence_no,FM_bearerid,
    lpad(dense_rank() over (partition BY district_code
    ||billing_account_no
    ||inst_sequence_no order by FM_bearerid),2,0)
    --FM_Bearerseqno
  FROM fld_map_isd n_bearer_24 b
  WHERE 'CSS'
    ||b.district_code
    ||b.billing_account_no IN ('CSSWM38199380', 'CSSWM34962678', 'CSSSM38710752', 'CSSWM37034992', 'CSSSW38485041', 'CSSWM38396668')

Re: update statement help [message #574692 is a reply to message #574673] Tue, 15 January 2013 01:06 Go to previous message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Thu, 22 November 2012 10:20
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals.
And find MEANINGFUL title.

Regards
Michel

Previous Topic: Bulk collect and for loop iteration error
Next Topic: how to convert varchar2 to number
Goto Forum:
  


Current Time: Tue Oct 21 01:21:39 CDT 2014

Total time taken to generate the page: 0.13483 seconds