troubleshooting Question

SQL Update Query

Avatar of hdcowboyaz
hdcowboyazFlag for United States of America asked on
SQL
6 Comments1 Solution278 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
hdcowboyaz

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros