help with temporary tables

ben_g

Can someone please suggest a good web site or book which describes in quiet simple terms how to create a temporary table in mysql and populate it with data from several select queries.

I have a query that returns a PlayerID, FirstName, SecondName.
And another that returns a PlayerID and a goal count.

I need to create a temporary table from the first query and then update some of the records with a goal count.

If anyone can help I would really appreciate it,

Ben

Lars_Berg

What's wrong with mysql online documentation? It's not good but mostly sufficient

http://www.mysql.com/doc/en/CREATE_TABLE.html

look at the examples with a select statement

ben_g

I can now see how a temporary table is created but how about filling it?

My table would look like so;

Temp(PlayerID, FirstName, SecondName, Goals)

I want to put the following querys results in the table;
SELECT PlayerID, FirstName, SecondName FROM Players

I then want to insert data into the Goals column from the following query;
SELECT Players.PlayerID, COUNT(Goals.PlayerID) From Goals LEFT JOIN Players ON Goals.PlayerID = Players.PlayerID GROUP BY Players.PlayerID

My problem is I don't know how to populate the table and then add extra data from new querys.

Lars_Berg

You can insert with a subselect

insert into temptable SELECT PlayerID, FirstName, SecondName, count( Goals.PlayerID) From Goals LEFT JOIN Players ON Goals.PlayerID = Players.PlayerID GROUP BY Players.PlayerID

ben_g

Thats good but if I have a couple more querys that return a playerID and one peice of data how do I update records to include them.

My table would be like;
temp(playerID, FirstName, SecondName, Data1, Data2)

and my second query would need to add data to the data1 column by matching playerID's.