help with mysql query

marcnyc

Hello,
I need help performing the following action on a table inside my database:
for every id (cal_id) that exists in my table I need to grab one piece of data from one column (cal_category) then delete all the other entries that have the same id and then create 5 all new entries with the category number previously found and new names in "cal_login" field.
If the id doesn't exist I can skip to the next id that exists.

In other words, for example, this:

5148  	studio  	A  	3
5148 	Stephen      A 	     3
5148 	Marc 		A 	3
5148 	Lou 		A 	3
5148 	Fran 		A 	3
5148 	Eric 		A 	3
5147 	Eric 		A 	1
5122 	Fran 		D 	2

needs to become this:

5148  	studio  	A  	3
5148 	Stephen 	A 	3
5148 	Marc 		A 	3
5148 	Lou 		A 	3
5148 	Fran 		A 	3
5148 	Eric 		A 	3
5148 	studio 		A 	3
5147 	Eric 		A 	1
5147 	Stephen 	A 	1
5147 	Marc 		A 	1
5147 	Lou 		A 	1
5147 	Fran 		A 	1
5147 	studio 		A 	1
5122 	Eric 		D 	2
5122 	Stephen 	D 	2
5122 	Marc 		D 	2
5122 	Lou 		D 	2
5122 	studio 		D 	2
5122 	Fran 		D 	2

Can anyone help me?

I am not proficient with MySQL but I sort of know that I can select the data I need like this:
SELECT cal_id, cal_category FROM webcal_entry_user
and I can insert the new lines like this (although I am not quite sure of this syntax):
INSERT INTO example (cal_id, cal_category) VALUES('studio', 'cal_category')
INSERT INTO example (cal_id, cal_category) VALUES('Marc', 'cal_category')
INSERT INTO example (cal_id, cal_category) VALUES('Fran', 'cal_category')
INSERT INTO example (cal_id, cal_category) VALUES('Eric', 'cal_category')
INSERT INTO example (cal_id, cal_category) VALUES('Lou', 'cal_category')

but I don't know how to put these things all together in one coherent MySQL query that will loop through all the IDs that exist in this table.

Thanks for your help.

tomhath

I suppose I'd do something like this:

CREATE TEMPORARY TABLE TempTable ( cal_id int, cal_category int) TYPE=HEAP;

insert into TempTable select distinct cal_id, cal_category from  example;

truncate table example;

insert into example (cal_id, name, cal_category) 
select cal_id, 'studio', cal_category from TempTable;

insert into example (cal_id, name, cal_category) 
select cal_id, 'Marc', cal_category from TempTable;

insert into example (cal_id, name, cal_category) 
select cal_id, 'Lou', cal_category from TempTable;

insert into example (cal_id, name, cal_category) 
select cal_id, 'Fran', cal_category from TempTable;

insert into example (cal_id, name, cal_category) 
select cal_id, 'Eric', cal_category from TempTable;

drop table TempTable;

marcnyc

Is CREATE TEMPORARY TABLE an actual MySQL syntax or just a comment?
Will this take only one instance of every row with the same ID?

tomhath

Did you try it?

CREATE TEMPORARY TABLE is valid. And the manual is your friend http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Should do what you want (if I understood the question). One row for each cal_id/name combination.

marcnyc

I thought I not have explained correctly. What I need is FIVE new rows for each cal_id/cal_category combination

tomhath

Five rows for each cal_id/cal_category.

Or saying it another way, one row for each cal_id/cal_category/name.

That's what you should get from what I posted,

marcnyc

Sorry for the late reply, work kept me away from this project.

I came back to it today and tried your code as follows:

CREATE TEMPORARY TABLE TempTable ( cal_id int, cal_category int) TYPE=HEAP;

insert into TempTable select distinct cal_id, cal_category from webcal_entry_user;

truncate table webcal_entry_user;

insert into webcal_entry_user (cal_id, cal_login, cal_status, cal_category)
select cal_id, 'studio', 'A', cal_category from TempTable;

insert into webcal_entry_user (cal_id, cal_login, cal_status, cal_category)
select cal_id, 'Marc', 'A', cal_category from TempTable;

insert into webcal_entry_user (cal_id, cal_login, cal_status, cal_category)
select cal_id, 'Lou', 'A', cal_category from TempTable;

insert into webcal_entry_user (cal_id, cal_login, cal_status, cal_category)
select cal_id, 'Fran', 'A', cal_category from TempTable;

insert into webcal_entry_user (cal_id, cal_login, cal_status, cal_category)
select cal_id, 'Eric', 'A', cal_category from TempTable;

drop table TempTable;

The result I got was:

Error

SQL query: Documentation

INSERT INTO webcal_entry_user( cal_id, cal_login, cal_status, cal_category )
SELECT cal_id, 'studio', 'A', cal_category
FROM TempTable;

MySQL said: Documentation
#1062 - Duplicate entry '1098-studio' for key 1

and the entire table is populated with 'studio' entries but none of the other 4 users (marc, Fran etc) are to be seen.

Are you able to see what the problem is?
Thanks