Question 20444 in Mysql Question posted in
The official MySQL documentation can be found here.

Not over-writing the data if a table is already present and creating table with data if it's not – Mysql

I’m trying to modify the dump file generated by MySQL (InnoDB engine) where I want to

  • check if a table is present
  • If it is already there then don’t overwrite the data
  • If it is not found then create a new one and fill up with default data

I can get each of the above two conditions to work separately but not with an IF statement.

This is an example script:

Table structure for table planned_objective

/*DROP TABLE IF EXISTS `planned_objective`*/;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `planned_objective` (
  `objective_id` int(11) NOT NULL AUTO_INCREMENT,
  `objective` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  `start_depth` float NOT NULL,
  `end_depth` float NOT NULL,
  `depth_uom` varchar(45) CHARACTER SET latin1 NOT NULL,
  `wellID` int(12) NOT NULL,
  PRIMARY KEY (`objective_id`,`start_depth`,`wellID`)
) ENGINE=InnoDB AUTO_INCREMENT=1195 DEFAULT CHARSET=utf8 COMMENT='details about planned objectives like casing, cementing, hole conditioning, tripping for BHA (agitator or change in steering unit) ';
/*!40101 SET character_set_client = @saved_cs_client */;

Dumping data for table planned_objective

LOCK TABLES `planned_objective` WRITE;
/*!40000 ALTER TABLE `planned_objective` DISABLE KEYS */;
INSERT INTO `planned_objective` VALUES (*VALUES TO BE WRITTEN*);
/*!40000 ALTER TABLE `planned_objective` ENABLE KEYS */;
UNLOCK TABLES;

The creation part is good but the dumping data section is where I probably need to have an IF statement, correct me if I’m wrong. Appreciate the help in advance!!

Tags:

2

Answers


  1. 0

    If you also insert the field with the primary key or of field with a unique key, the you can use INSERT IGNORE ….. This will only insert new Rows

    INSERT IGNORE INTO `planned_objective` VALUES (*VALUES TO BE WRITTEN*);
    Login or Signup to reply.
  2. 0

    Here is a other trick you can do it or you must used a Stored Procedure

    1. create your table if not exists

    2. create a tmp table with same structure

    3. import the init data into the tmp table

    4. check if main table has rows and store the result in a var

    5. insert int main table only if was empty
      6 clean up tmp table

       CREATE TABLE IF NOT EXISTS `planned_objective` (
 `objective_id` int(11) NOT NULL AUTO_INCREMENT,
 `objective` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
 `start_depth` float NOT NULL,
 `end_depth` float NOT NULL,
 `depth_uom` varchar(45) CHARACTER SET latin1 NOT NULL,
 `wellID` int(12) NOT NULL, PRIMARY KEY(`objective_id`,`start_depth`,`wellID`)) ENGINE=InnoDB AUTO_INCREMENT=1195 DEFAULT CHARSET=utf8 COMMENT='details about planned objectives like casing, cementing, hole conditioning, tripping for BHA (agitator or change in steering unit) ';


 DROP TABLE IF EXISTS `tmp_planned_objective`;

 CREATE TABLE IF NOT EXISTS `tmp_planned_objective` (
   `objective_id` int(11) NOT NULL AUTO_INCREMENT,
   `objective` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
   `start_depth` float NOT NULL,
   `end_depth` float NOT NULL,
   `depth_uom` varchar(45) CHARACTER SET latin1 NOT NULL,
   `wellID` int(12) NOT NULL,
   PRIMARY KEY (`objective_id`,`start_depth`,`wellID`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1195 DEFAULT CHARSET=utf8 COMMENT='details about planned objectives like casing, cementing, hole conditioning, tripping for BHA (agitator or change in steering unit) ';


 /* insert init data into tmp table */
 INSERT INTO `tmp_planned_objective` (`objective_id`, `objective`, `start_depth`, `end_depth`, `depth_uom`, `wellID`)
 VALUES
     (1, '2', 3, 4, '5', 6),
     (2, '2', 3, 4, '5', 6),
     (3, '2', 3, 4, '5', 6),
     (4, '2', 3, 4, '5', 6),
     (5, '2', 3, 4, '5', 6),
     (6, '2', 3, 4, '5', 6),
     (7, '2', 3, 4, '5', 6);

      /* check if empty */

      SELECT count(*) INTO @emptyflag FROM planned_objective;

      SELECT @emptyflag; /* only for debug */

      /* copy only if table empty */

      INSERT INTO planned_objective
      SELECT * from tmp_planned_objective WHERE @emptyflag = 0;

      /* rempve tmp table */

      DROP TABLE IF EXISTS tmp_planned_objective;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search