=================================t===============================
(1) create main  table - inially has the same columns. 
   later on add a new column to store ID values 
   then set as foerigh key
=================================================================

CREATE TABLE `emp_info_main` (
  `Last_Name` text,
  `City` text,
  `Annual_Income` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `employee`.`emp_info_main` 
ADD COLUMN `dept_id` INT NULL AFTER `Annual_Income`;



=================================================================
(2) LOad data into main table, readyfor further process
==================================================================

insert into emp_info_main
SELECT *, null FROM employee.emp_info;


================================================================
(3) create definition table, iys ID column will be the 
  target of foregn key column in main table.   prepare 
  for a relational database design.
=================================================================

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



=================================================================
(4) extract data

load definition data into def table, and set null to 
 the AI PK ID column, the values in the column will 
 be auto set itself
=================================================================

insert into employee.dept
SELECT distinct null, city FROM employee.emp_info;

=================================================================
(5) dynamic sq;l to create a query string with variable
=================================================================

			set @table_name:= 'world.city';
		
			set @sql:= concat( 'select * from ', @table_name );
							
			 PREPARE dynamic_statement FROM @sql;
			 EXECUTE dynamic_statement;
			 DEALLOCATE PREPARE dynamic_statement;
             

=================================================================
(6) write id back  to maiun using inner join 
=================================================================

uupdate employee.emp_info_main a
       inner join employee.dept b
       on a.city = b.dept_name
       
	set a.dept_id = b.id
    where a.city = b.dept_name
	
	
	=================================================================
(7) wrut3 a before update trigger
=================================================================

	
	CREATE DEFINER=`root`@`localhost` TRIGGER `country_BEFORE_UPDATE` BEFORE UPDATE ON `country` FOR EACH ROW BEGIN

	INSERT INTO audit_country 
    ( old_val, new_val, updated_by, updated_on, code) 
    values (  OLD.code,
           new.code,
             user(),
             now(),
             old.code
         );
END
  
       















;