New table row autopopulated with data from other rows? I am in the middle of developing a simulated horse game using PHP and databases. Apparently I have just enough knowledge to get into trouble with my grand ideas! I'm looking for help/tips on my current task, creating new horse stats based on currently registered ones in a database.

I have a horse table containing data similar the following:

CREATE TABLE horse(id int, name text, stat1 int);
INSERT INTO horse VALUES
  (1,'sparky',20),
  (2,'spunky',10);


I want to create a new horse using the average of the horse 1 &amp; 2's values contained in stat1. So horse 3 would have a stat1 value of 15 that would be automatically calculated.

Is this possible using PHP? Or can someone point me in the right direction?
If you want the average for all horses:

insert into horse (id, name, stat1)
select nextid, 'name', avg(stat1)
from horse;


If you want the average for a subset of horses you can add a where clause:

insert into horse (id, name, stat1)
select nextid, 'name', avg(stat1)
from horse
where ... ;


If id is autogenerated:

insert into horse (name, stat1)
select 'name', avg(stat1)
from horse
where ... ;