MySQL: Identify duplicates and replace values

Platform: MySQL Workbench 8.0 CE SQL: 5.5.57

I'm having trouble creating the proper statement for following task:

Let's say we have a table 'departments'

| id | name | superior_dep_id |
_______________________________
  1  | SM   |  null          
  2  | SMT  |  1      
  3  | SMTE |  2 
  4  | SMI  |  1      
  5  | SM   |  null

and i've just added a new department 'SM' which will replace the old department, in terms of validity only, so the old one still remains unchanged.

Now i'd need identify all departments, which contain the old departments.id at departments.superior_dep_id and replace them with the new departments.id = 5.

| id | name | superior_dep_id |
_______________________________
  1  | SM   |  null          
  2  | SMT  |  1      <--- 5
  3  | SMTE |  2 
  4  | SMI  |  1      <--- 5
  5  | SM   |  null   <--- INSERT INTO 
                           departments(id, name, superior_dep_id) 
                           VALUES(5, SM, null);

Thanks in advance.

CodePudding user response：

First I guess you will have to indentify the last departments.id inserted by name, skipping the last one you inserted:

select departments.id from departments where name = 'SM' and departments.id <> 5 ORDER BY departments.id DESC LIMIT 1;

After you get the departments.id (which will be 1), you can update the fields:

update departments set superior_dep_id = 5 where superior_dep_id = 1;