create schema if not exists temp ; USE temp; DROP TABLE IF EXISTS tbl_ne2; CREATE TABLE tbl_ne2 ( `OBJECT_NAME` varchar(100) NOT NULL, `LOCATION` varchar(45) NOT NULL, `OBJECT_TYPE` varchar(5) DEFAULT NULL, PRIMARY KEY (`OBJECT_NAME`) USING BTREE, KEY `Index_2` (`OBJECT_TYPE`,`LOCATION`) USING BTREE ) ENGINE=InnoDB; insert into tbl_ne2 values ('A1' , 'A' , 'P'); insert into tbl_ne2 values ('B1' , 'B' , 'P'); DROP TABLE IF EXISTS tbl_ont_inventory2; CREATE TABLE tbl_ont_inventory2 ( `OBJECT_NAME` varchar(200) NOT NULL, `PARENT` varchar(100) NOT NULL, PRIMARY KEY (`OBJECT_NAME`) USING BTREE ) ENGINE=InnoDB; insert into tbl_ont_inventory2 values ('A11' , 'A1'); insert into tbl_ont_inventory2 values ('B11' , 'B1'); insert into tbl_ne2 (OBJECT_NAME, LOCATION, OBJECT_TYPE) SELECT a.OBJECT_NAME, b.LOCATION, 'C' OBJECT_TYPE FROM tbl_ne2 b inner join tbl_ont_inventory2 a on b.object_name = a.PARENT where b.object_type = 'P' on duplicate key update tbl_ne2.LOCATION = b.LOCATION; select * from tbl_ne2 ; insert into tbl_ne2 (OBJECT_NAME, LOCATION, OBJECT_TYPE) SELECT a.OBJECT_NAME, b.LOCATION, 'C' OBJECT_TYPE FROM tbl_ne2 b inner join tbl_ont_inventory2 a on b.object_name = a.PARENT where b.object_type = 'P' on duplicate key update tbl_ne2.LOCATION = b.LOCATION; select * from tbl_ne2 ;