Question 20776 in Mysql Question posted in
The official MySQL documentation can be found here.

Update duplicate records with incremental column – Mysql

— create

CREATE TABLE employee 
(
  emp_id INTEGER,
  job_code TEXT,
  cnt_check int
);

— insert

INSERT INTO employee(emp_id,job_code) VALUES (0001, 'JC001');
INSERT INTO employee(emp_id,job_code) VALUES (0001, 'JC001');
INSERT INTO employee(emp_id,job_code) VALUES (0002, 'JC002');
INSERT INTO employee(emp_id,job_code) VALUES (0002, 'JC002');
INSERT INTO employee(emp_id,job_code) VALUES (0003, 'JC003');
INSERT INTO employee(emp_id,job_code) VALUES (0004, 'JC004');
INSERT INTO employee(emp_id,job_code) VALUES (0004, 'JC004');
INSERT INTO employee(emp_id,job_code) VALUES (0004, 'JC004');

Expected Output:

emp_id  job_code  cnt_check
--------------------------------
0001    JC001       1
0001    JC001       2
0002    JC002       1
0002    JC002       2
0003    JC003       1
0004    JC004       1
0004    JC004       2
0004    JC004       3

My try:

update employee t1
join 
(
select emp_id ,job_code ,row_number() over(partition by emp_id ,job_code ) rnk
from employee
) t2 
on t1.emp_id = t2.emp_id and t1.job_code = t2.job_code 
set t1.cnt_check = t2.rnk;

But all records getting updated with value 1.

Demo link

Tags:

2

Answers


  1. 0

    Using the exact columns in your sample data, we can try:

    SELECT emp_id, job_code,
       ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_id) AS cnt_check
FROM employee
ORDER BY 1, 3;

    Note that I suggest not doing this update, as every time your data changes you might have to run the update again.

    Login or Signup to reply.
  2. 0

    You may use generation which uses BEFORE INSERT trigger and additional table:

    CREATE TABLE autoinc_helper (
  emp_id INT,
  cnt_check INT AUTO_INCREMENT,
  PRIMARY KEY (emp_id, cnt_check)
) ENGINE = MyISAM;

    CREATE TRIGGER generate_autoinc
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
  DECLARE autoinc INT;
  INSERT INTO autoinc_helper (emp_id) VALUES (NEW.emp_id);
  SET autoinc = LAST_INSERT_ID();
  SET NEW.cnt_check = autoinc;
  DELETE FROM autoinc_helper WHERE emp_id = NEW.emp_id AND cnt_check < autoinc;
END

    DEMO fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search