Re: INSERT AfTER LEFT JOIN creates duplicate records

[Brad Liu]

Below are the tables descriptions:

CREATE TABLE `so_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`so_num` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`cust_num` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`part_num` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`descrip` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
`disc` decimal(10,3) DEFAULT NULL,
`cost` decimal(10,3) DEFAULT NULL,
`price` decimal(10,3) DEFAULT NULL,
`qtyord` int(11) DEFAULT NULL,
`qtyshp` int(11) DEFAULT NULL,
`extprice` decimal(10,3) DEFAULT NULL,
`ord_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`req_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`shp_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`dealer_type` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`part_cls` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`location` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
`sostat` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`discls` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`wh_id` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `so_num` (`so_num`),
KEY `cust_num` (`cust_num`),
KEY `part_num` (`part_num`),
KEY `descrip` (`descrip`),
KEY `disc` (`disc`),
KEY `cost` (`cost`),
KEY `price` (`price`),
KEY `qtyord` (`qtyord`),
KEY `qtyshp` (`qtyshp`),
KEY `ord_date` (`ord_date`),
KEY `req_date` (`req_date`),
KEY `shp_date` (`shp_date`),
KEY `part_cls` (`part_cls`),
KEY `location` (`location`),
KEY `sostat` (`sostat`),
KEY `discls` (`discls`),
KEY `wh_id` (`wh_id`),
KEY `dealer_type` (`dealer_type`),
KEY `extprice` (`extprice`),
CONSTRAINT `soHeaderSo_soDetailSo_fk` FOREIGN KEY (`so_num`) REFERENCES `so_header` (`so_num`)
) ENGINE=InnoDB AUTO_INCREMENT=834718 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `SOTRAN` (
`sono` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`custno` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
`item` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`descrip` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
`disc` double NOT NULL,
`cost` double NOT NULL,
`price` double NOT NULL,
`qtyord` double NOT NULL,
`qtyshp` double NOT NULL,
`extprice` double NOT NULL,
`ordate` date NOT NULL,
`rqdate` date NOT NULL,
`shipdate` date NOT NULL,
`terr` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`class` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`seq` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
`sostat` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
`disclass` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
KEY `sono` (`sono`),
KEY `item` (`item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Below are the table duplicates:
826830 370457 ****** 52562-06130 RETAINER, RR BUMPER, 25.000 4.216 8.260 2 0 12.390 2016-09-12 13:47:49 2016-09-12 13:42:18 0000-00-00 00:00:00 TY A0 311D 1
826831 370457 ****** 52562-06130 RETAINER, RR BUMPER, 25.000 4.216 8.260 2 0 12.390 2016-09-12 13:47:49 2016-09-12 13:42:18 0000-00-00 00:00:00 TY A0 311D 1

Thank you!