Extract data to new columns

Question

Hi guys, I am a newbie in MySQL. I created a new table "competitiveness" in smartform databa,se created a new column "number_of_races", I can make the count of races per horse:

select name, count(*) from historic_runners group by name;

 but my question is how do I put that count value in number_of_races that is is in the table competitiveness?

Regards

Answer 1

You need to set up your "competitiveness" table with fields that match the result of your query. So on the basis of the above you'd want name as VARCHAR(255) and number_of_races as INT. You can then do an INSERT SELECT command using your select query

Syntax might be something like:

DROP TABLE IF EXISTS competitiveness;

CREATE TABLE competitiveness (
  `runner_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `number_of_races` INT NOT NULL,
  PRIMARY KEY (`runner_id`));

INSERT INTO competitiveness
select runner_id, name, count(runner_id) from historic_runners
where ((unfinished not like _latin1'Non%') or isnull(unfinished))
group by runner_id, name;

I've put the runner_id in as well as the runner name, because that's the obvious Primary Key for the new table. The where clause in the Insert Select statement is to remove records where the horse was a non-runner.

Haven't tested the above but hope it's of some help. Good luck.