CODEWITHSUNDEEP
mysqlprocedure
Bes Sa
Bes Sa

Reputation: 93

How to process data from one table and isert it to another table in MySQL?

I want to insert values from one of my tables to another.

I have a following table:

  Act (
  id  INT NOT NULL AUTO_INCREMENT,
  type VARCHAR(20),
  Act_date DATETIME,
  place VARCHAR(20)  
  );

where type has 2 possible values: "birth" and "death". All the columns are already filled. Now, I am trying to make a procedure or a function which would calculate the difference between Acts of type "birth" and acts of type "death" for a given time and put the results in a different table, something like:

  Population_Growth(
  id INT NOT NULL AUTO_INCREMENT,
  time DATE,
  place VARCHAR(20),
  result INT
  );

where result would be the difference between Acts of type "birth" and acts of type "death"

I would appreciate any help.

Upvotes: 0

Views: 62

Answers (1)

Devon Bessemer
Devon Bessemer

Reputation: 35347

This was too long to write in a comment, it's not an answer for your problem as it is now, but it is what I recommend. Why not just have a column for birth and a column for death in the same table? Personally I would do this:

  Act (
  id  INT NOT NULL AUTO_INCREMENT,
  birth_date DATETIME,
  death_date DATETIME,
  place VARCHAR(20)  
  );

  Population_Growth(
  id INT NOT NULL,
  result INT
  );

Have ID as the primary key in both, ID as a foreign key in population growth. You don't need anything else but the result in Population_Growth as you can refer to the ID in Act for the place. Avoid duplicate data in SQL unless you have a good reason to do otherwise.

Then you can do an insert using the information from the Act table. TIMESTAMPDIFF will return an integer of the difference between the two dates. I'm not sure if you want years, months, seconds, etc.

INSERT INTO Population_Growth 
SELECT id, TIMESTAMPDIFF(YEAR, death_date, birth_date) FROM Act 
WHERE death_date IS NOT NULL AND birth_date IS NOT NULL;

Upvotes: 1

Related Questions