CODEWITHSUNDEEP
sqloracle-databaserowauto-increment
user3191666
user3191666

Reputation: 159

Update row value for column with increment on Oracle Database

I am trying to write a stored procedure with an update on Oracle database where I want the 'labelcount' to be incremented by one.

MEMBER_NUM field is not allowed the same value twice.

Any advice?

labelcount:= 1


 IF (major_count >=1 ) and (min_count >=1 ) then
          UPDATE  TBL_MEMBERS
          SET     MEMBER_NUM = Major || 'M' || labelcount
          WHERE   MEMBER_ID = Minor and MEMBER_NUM = 'XXX';

 End If;

So this:

Member_ID   | Member_NUM    |
A       | xxx       |
A       | 4444      |
A       | xxx       |
A       | xxx       |

Becomes 

Member_ID   | Member_NUM    |
A       | AM1       |
A       | 4444      |
A       | AM2       |
A       | AM3       |

Upvotes: 0

Views: 2827

Answers (3)

am2
am2

Reputation: 371

First some rows to build the example

-- Example Table
CREATE TABLE TBL_MEMBERS 
( 
  MEMBER_ID CHAR(1),
  MEMBER_NUM CHAR(5)
)
/
-- Example Data
delete from TBL_MEMBERS;
INSERT INTO TBL_MEMBERS (
  SELECT 'A','xxx' FROM DUAL UNION ALL
  SELECT 'A','xxx' FROM DUAL UNION ALL
  SELECT 'A','xxx' FROM DUAL UNION ALL
  SELECT 'A','1234' FROM DUAL UNION ALL
  SELECT 'A','xxx' FROM DUAL UNION ALL
  SELECT 'A','5678' FROM DUAL 
)
/

Data:

MEMBER_ID|MEMBER_NUM
A        |xxx
A        |xxx
A        |xxx
A        |1234
A        |xxx
A        |5678

-- This is the way you should use
--DROP sequence AI_MEMBERS;
CREATE SEQUENCE AI_MEMBERS; 
-- Here you can try it out, but attention
-- each select increases the number
SELECT AI_MEMBERS.NEXTVAL FROM DUAL;

-- Your update (without your minor/major ..., cause it is unimportant for explanation
UPDATE  TBL_MEMBERS
SET     MEMBER_NUM = 'A' || 'M' || AI_MEMBERS.NEXTVAL
WHERE   MEMBER_ID = 'A' and MEMBER_NUM = 'XXX'
;

Result:

MEMBER_ID|MEMBER_NUM
A        |AM1
A        |AM2
A        |AM3
A        |1234
A        |AM4
A        |5678

Upvotes: 2

Migs
Migs

Reputation: 1488

not quite sure i understand your requirement due to the "4444" value on the Member_Num Column but i'd give it a shot:

DDL:

Create Table TBL_MEMBERS
(
    Member_ID       number
  , Member_NUM      varchar2(100)
);

create sequence labelcount
start with 1;

CREATE or replace PROCEDURE INC_LABEL_COUNT (P_major_count NUMBER, p_min_count NUMBER)
AS

    L_STAGE VARCHAR2(100);

BEGIN

    L_STAGE := 'Checking for Count Values';

    IF (p_major_count >=1 ) and (p_min_count >=1 ) then

          UPDATE  TBL_MEMBERS
          SET     MEMBER_NUM = 'Major' || 'M' || labelcount.nextval
          WHERE   MEMBER_ID =  'Minor'              
          and     MEMBER_NUM = 'XXX';

    End If; 


EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in Stage: '||L_STAGE||' '||sqlerrm);        
END INC_LABEL_COUNT;

That's as far as i can go, since that's the only information I have.

Can you provide more?

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21053

You are not very precise, so the answer can't be exact as well

To avoid duplicates in MEMBER_NUMcolumn use unique index on it.

The update will therefore fail if the increased number already exists in DB.

Preferably use explicite column for minor and lablecount (not a string concatenation)

Upvotes: 2

Related Questions