0VV5cj2E

1Delayed insert due to foreign key constraints
2INSERT
3  INTO `ProductState` (`ProductId`, `ChangedOn`, `State`)
4SELECT t.`ProductId`, t.`ProcessedOn`, 'Activated'
5  FROM `tmpImport` t
6  LEFT JOIN `Product` p
7    ON t.`ProductId` = p.`Id`
8 WHERE p.`Id` IS NULL
9    ON DUPLICATE KEY UPDATE
10       `ChangedOn` = VALUES(`ChangedOn`)
11	
12CREATE  TABLE IF NOT EXISTS `ProductState` (
13  `ProductId` VARCHAR(32) NOT NULL ,
14  `ChangedOn` DATE NOT NULL ,
15  `State` ENUM('Activated','Deactivated') NULL ,
16  PRIMARY KEY (`ProductId`, `ChangedOn`) ,
17  INDEX `fk_ProductState_Product` (`ProductId` ASC) ,
18  CONSTRAINT `fk_ProductState_Product`
19    FOREIGN KEY (`ProductId` )
20    REFERENCES `Product` (`Id` )
21    ON DELETE NO ACTION
22    ON UPDATE NO ACTION)
23ENGINE = InnoDB
24DEFAULT CHARACTER SET = utf8
25COLLATE = utf8_general_ci;
26	
27CREATE PROCEDURE `some_procedure_name` ()
28BEGIN
29
30-- Breakdown the tmpImport table to 2 tables: new and removed
31SELECT * INTO _temp_new_products
32FROM`tmpImport` t
33LEFT JOIN `Product` p
34ON t.`ProductId` = p.`Id`
35WHERE p.`Id` IS NULL
36
37SELECT * INTO _temp_removed_products
38FROM `Product` p
39LEFT JOIN `tmpImport` t 
40ON t.`ProductId` = p.`Id`
41WHERE t.`ProductId` IS NULL
42
43-- For each entry in _temp_new_products:
44-- 1. Insert into Product table
45-- 2. Insert into ProductState table 'activated'
46
47-- For each entry in _temp_removed_products:
48-- 1. Insert into ProductState table 'deactivated'
49
50-- drop the temporary tables
51DROP TABLE _temp_new_products
52DROP TABLE _temp_removed_products
53END