How to UPDATE by COUNT from JOINed tables I have a schema as

CREATE TABLE companies
(
company_id int(11) NOT NULL AUTO_INCREMENT,
company varchar(255),
projects int(11),
PRIMARY KEY(company_id)
) ENGINE=InnoDB

CREATE TABLE employees
(
employee_id int(11) NOT NULL AUTO_INCREMENT,
company_id int(11),
name varchar(255),
PRIMARY KEY(employee_id)
) ENGINE=InnoDB

CREATE TABLE project_map
(
employee_id int(11),
project_id int(11),
PRIMARY KEY(employee_id,project_id)
) ENGINE=InnoDB


I need to UPDATE column projects in table company, by counting all projects done by employees of each company.

UPDATE companies
SET project = count of project_map by employees of each company
I did manage to do this through SQL. However, I think that this is a task that is far better better suited for TRIGGERs - i.e. if a record insertion/deletion is made on the project_map table, then a TRIGGER should fire adding +/- 1 to the relevant company's project field. I gave this a +1 because it was trickier than it seems initially.

Giving the DDL was good, but it would have been even better if you had also provided DML (which I had to create myself for testing). I've put my DDL + DML at the bottom of this post.

The SQL is: 

UPDATE companies c
JOIN
(
  SELECT cc.company_id AS cid, SUM(cc.pcount) AS pcount
  FROM
  (
    SELECT e.company_id, ep.eid, ep.pcount
    FROM employees e
    INNER JOIN
    (
      SELECT e.employee_id AS eid, COUNT(p.employee_id) AS pcount
      FROM project_map p
      INNER JOIN
        employees e
        ON p.employee_id = e.employee_id
      GROUP BY eid
      ORDER BY eid
    ) AS ep
    ON e.employee_id = ep.eid
  ) AS cc
  GROUP BY cid
  ORDER BY cid
) AS b
ON c.company_id = b.cid
SET c.projects = b.pcount;


The many derived tables in the above query are not necessary but they can serve as an illustration of the step by step process of how the query was developed. The actual query can be as simple as this:

UPDATE companies c
JOIN
(
  SELECT e.company_id AS cid, COUNT(p.employee_id) AS pcount
  FROM project_map p
  INNER JOIN
    employees e
    ON p.employee_id = e.employee_id
  GROUP BY cid
) AS b
ON c.company_id = b.cid
SET c.projects = b.pcount;


And the result in either case is:

company_id, company,  projects
1,          companyA,        4
2,          companyB,        4
3,          companyC,        1
4,          companyD,        0


It is relatively easy to see from the DML (below) that this is the correct result.

-------------------- DDL and DML ----------------------

DML

This is a minimal "implementation" - of course, one would have to create PRIMARY KEYs, FOREIGN KEYs, NOT NULLs, UNIQUE KEYs and just plain old KEYs.

CREATE TABLE companies
(
company_id INTEGER,
company VARCHAR(255),
projects INTEGER
);


CREATE TABLE employees
(
employee_id INTEGER,
company_id INTEGER,
name VARCHAR(255)
);


CREATE TABLE project_map
(
employee_id INTEGER,
project_id INTEGER
);


And the DML for the test data:

INSERT INTO  companies (company_id, company, projects) VALUES (1, 'companyA', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (2, 'companyB', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (3, 'companyC', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (4, 'companyD', 0);


INSERT INTO employees (employee_id, company_id, name) VALUES (1, 1, 'emp1_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (2, 1, 'emp1_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (3, 2, 'emp2_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (4, 2, 'emp2_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (5, 3, 'emp3_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (6, 3, 'emp3_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (7, 5, 'emp5_1');


INSERT INTO project_map VALUES(1, 15);
INSERT INTO project_map VALUES(1, 16);
INSERT INTO project_map VALUES(1, 17);
INSERT INTO project_map VALUES(2, 26);
INSERT INTO project_map VALUES(3, 35);
INSERT INTO project_map VALUES(3, 36);
INSERT INTO project_map VALUES(3, 37);
INSERT INTO project_map VALUES(3, 38);
INSERT INTO project_map VALUES(5, 55);