asked on

SQL Update Query

The select query inside the Update query works...

SELECT d.est_id, SUM(d.E_AMT) AS samount
FROM est, estdetail d, prtrans prt, project pj, releases r, task t
WHERE d.est_id = est.est_id
AND pj.project_id = r.project_id
AND r.releases_id = t.releases_id
AND t.task_id = d.task_id
GROUP BY d.est_id;

However, when I run the Update query below I get the following error...

Query : UPDATE project pj, ( SELECT d.est_id, SUM(d.E_AMT) AS samount FROM est, estdetail d, prtrans prt, project pj, releases r, t...
Error Code : 1054
Unknown column 'd.est_id' in 'where clause'

UPDATE project pj,
      (
      SELECT d.est_id, SUM(d.E_AMT) AS samount
      FROM est, estdetail d, prtrans prt, project pj, releases r, task t
      WHERE d.est_id = est.est_id
      AND pj.project_id = r.project_id
      AND r.releases_id = t.releases_id
      AND t.task_id = d.task_id
      GROUP BY d.est_id
      ) z
SET pj.E_AMT = z.samount
WHERE z.est_id = d.est_id;

peter57r

You don't have a table d.

Your tables for your join are pj and z

hdcowboyaz

ASKER

FROM est, estdetail d, prtrans prt, project pj, releases r, task t

hdcowboyaz

ASKER

I have tried...

UPDATE project pj,
      (
      SELECT d.est_id, SUM(d.E_AMT) AS samount
      FROM est, estdetail d, prtrans prt, project pj, releases r, task t
      WHERE d.est_id = est.est_id
      AND pj.project_id = r.project_id
      AND r.releases_id = t.releases_id
      AND t.task_id = d.task_id
      GROUP BY d.est_id
      ) z
SET pj.E_AMT = z.samount
WHERE z.project_id = pj.project_id;

but get ther error...............
Query : UPDATE project pj, ( SELECT d.est_id, SUM(d.E_AMT) AS samount FROM est, estdetail d, prtrans prt, project pj, releases r, t...
Error Code : 1054
Unknown column 'z.project_id' in 'where clause'

ASKER CERTIFIED SOLUTION

hdcowboyaz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Why not GROUP BY project_id? It seems odd to GROUP BY d.est_id only, which works in MySQL but may not be returning the project_id desired if there is more than one per est_id. Therefore, I would recommend you simplify the query to this:

UPDATE project pj, 
      (
      SELECT r.project_id, SUM(d.E_AMT) AS samount 
      FROM estdetail d, releases r, task t
      WHERE r.releases_id = t.releases_id
      AND t.task_id = d.task_id
      GROUP BY r.project_id
      ) z
SET pj.E_AMT = z.samount
WHERE z.project_id = pj.project_id;

Open in new window

Note: since project is what you are updating and it is connected through releases.project_id, the project JOIN in the derived table is likely unnecessary and so I removed that also. The same for est. It did not appear to serve any purpose at all. You can always run the two SELECT statements and ensure that the new version is returning the correct SUM values before doing the update (that is always a good idea anyway before updating production data by the way).

hdcowboyaz

ASKER

...