CODEWITHSUNDEEP
mysql
Richard
Richard

Reputation: 733

Insert all table in another table with different structure

I am using mysql and I have a table with the following mysql> describe activities;

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| year       | int(11)          | NO   | MUL | NULL    |                |
| month      | int(11)          | NO   |     | NULL    |                |
| project_id | int(10) unsigned | NO   | MUL | NULL    |                |
| user_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| task_hour  | double(8,2)      | NO   |     | NULL    |                |
| from_otl   | tinyint(1)       | NO   |     | 0       |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

This gives me for a specific year, a specific month on a specific project for a specific user, the ammount of hours worked.

I will need to create a table with a different structure with data from this last table. This will have the same columns but instead of month and task hours, I will have each month as a column and the hours worked in this column.

Here is what I tried:

CREATE TEMPORARY TABLE temp_a
(
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
year INT(10),
project_id INT(10),
user_id INT(10),
jan_com double(8,2),
jan_otl tinyint(1),
feb_com double(8,2),
feb_otl tinyint(1),
mar_com double(8,2),
mar_otl tinyint(1),
apr_com double(8,2),
apr_otl tinyint(1),
may_com double(8,2),
may_otl tinyint(1),
jun_com double(8,2),
jun_otl tinyint(1),
jul_com double(8,2),
jul_otl tinyint(1),
aug_com double(8,2),
aug_otl tinyint(1),
sep_com double(8,2),
sep_otl tinyint(1),
oct_com double(8,2),
oct_otl tinyint(1),
nov_com double(8,2),
nov_otl tinyint(1),
dec_com double(8,2),
dec_otl tinyint(1)
);
ALTER TABLE `temp_a` ADD UNIQUE( `year`,`project_id`, `user_id`);
INSERT INTO temp_a (`year`,`project_id`,`user_id`) VALUES (SELECT `year`,`project_id`,`user_id` FROM `activities` group by `year`,`project_id`,`user_id`);
select * from temp_a;

But it doesn't accept the insert into ...

What I wanted to do is first fill in all the unique year, project_id, user_id into this temp table then update each record by going through the activities table and update the correct row with the value for the right month.

Upvotes: 0

Views: 32

Answers (1)

flyingfox
flyingfox

Reputation: 13506

You need to remove VALUES when use SELECT in INSERT INTO

  INSERT INTO temp_a (`year`,`project_id`,`user_id`) 
    (SELECT `year`,`project_id`,`user_id` FROM `activities` group by `year`,`project_id`,`user_id`);

Upvotes: 2

Related Questions