CODEWITHSUNDEEP
oracle-sqldeveloper
Hbk88
Hbk88

Reputation: 65

oracle stop select duplicated value

I am trying to insert data in my table from another which has two column (employee number) and (branch), and whenever new data is inserted the employee last number value is increased,

my code is working fine but if the there are more than one employee inserted at the same time they will have duplicated value.

for example, if I inserted the data with branch number is 100 the employee will have number 101, and if the branch number is 200 the employee will have number 201 etc.

but if data inserted for two employees both have same branch for example number 200 both of them will have number 201, but I want the first one to have 201 and the second one to have 202,

I hope you get what I mean, any help will be appreciated.

here is my code:

insert into emp_table_1
Emp_Name_1, 
Emp_Branch_1,
Emp_number_1

Select Emp_Name_2 , 
Emp_Branch_2,
Case emp_branch
When '100' Then (Select Max(Emp_number_1)+1 From emp_table_1 Where Branch_Cd=100)
When '200' Then (Select Max(Emp_number_1)+1 From emp_table_1 Where Branch_Cd=200)

End As Emp_number_2
From emp_table_2

Upvotes: 0

Views: 18

Answers (1)

MT0
MT0

Reputation: 168796

Don't try to have sequential numbers for each branch and don't try to use MAX to find the next number in the sequence.

Use a sequence (that is what they are designed for).

CREATE SEQUENCE employee_id__seq;

Then you can use:

insert into emp_table_1 (Emp_Name_1, Emp_Branch_1, Emp_number_1)
Select Emp_Name_2 , 
       Emp_Branch_2,
       employee_id__seq.NEXTVAL
From   emp_table_2

Then each employee will have a unique number (which you can use as a primary key) and you will not get concurrency issues if multiple people try to create new users at the same time.

Or, from Oracle 12, you could use an identity column in your table:

CREATE TABLE emp_table_1(
  emp_name_1   VARCHAR2(200),
  emp_branch_1 CONSTRAINT emp_table_1__branch__fk REFERENCES branch_table (branch_id),
  emp_number_1 NUMBER(8,0)
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT emp_table_1__number__pk PRIMARY KEY
);

Then your query is simply:

insert into emp_table_1 (Emp_Name_1, Emp_Branch_1)
Select Emp_Name_2 , 
       Emp_Branch_2
From   emp_table_2

And the identity column will be auto-generated.

Upvotes: 2

Related Questions