?
Solved

Query Syntax

Posted on 2012-08-25
5
Medium Priority
?
480 Views
Last Modified: 2012-08-25
This works...

SELECT pcac
FROM pcac pc
JOIN
      (SELECT MAX(pcac_id) AS pcac_id
      FROM pcac
      WHERE deleted = 'N'
      GROUP BY estdetail_id
      ORDER BY estdetail_id ASC
      ) AS pc2 ON pc2.pcac_id = pc.pcac_id
ORDER BY estdetail_id ASC;

This update doesn't

UPDATE estdetail d,
      (
      SELECT pcac AS samount
      FROM pcac pc
            JOIN
                  (SELECT MAX(pcac_id) AS pcac_id
                  FROM pcac
                  WHERE deleted = 'N'
                  GROUP BY estdetail_id
                  ORDER BY estdetail_id ASC
                  ) AS pc2 ON pc2.pcac_id = pc.pcac_id
      ) z
SET d.PCAC_EST = z.samount
WHERE z.estdetail_id = d.estdetail_id
ORDER BY d.estdetail_id ASC;
0
Comment
Question by:hdcowboyaz
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38333312
Your SELECT does not return estdetail_id ... which is needed for the join "WHERE z.estdetail_id = d.estdetail_id".
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38333313
perhaps ...

UPDATE estdetail d,
      (
      SELECT estdetail_id, pcac AS samount
      FROM pcac pc
            JOIN
                  (SELECT MAX(pcac_id) AS pcac_id
                  FROM pcac
                  WHERE deleted = 'N'
                  GROUP BY estdetail_id
                  ORDER BY estdetail_id ASC
                  ) AS pc2 ON pc2.pcac_id = pc.pcac_id
      ) z
SET d.PCAC_EST = z.samount
WHERE z.estdetail_id = d.estdetail_id
ORDER BY d.estdetail_id ASC;
0
 

Author Comment

by:hdcowboyaz
ID: 38333335
i get the same error

Error Code : 1221
Incorrect usage of UPDATE and ORDER BY
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38333341
Why do you need the order by?
0
 

Author Comment

by:hdcowboyaz
ID: 38333363
I guess I didn't. I took it out and it worked.

UPDATE estdetail d,
      (
      SELECT estdetail_id, pcac AS samount
      FROM pcac pc
            JOIN
                  (SELECT MAX(pcac_id) AS pcac_id
                  FROM pcac
                  WHERE deleted = 'N'
                  GROUP BY estdetail_id
                  ) AS pc2 ON pc2.pcac_id = pc.pcac_id
      ) z
SET d.PCAC_EST = z.samount
WHERE z.estdetail_id = d.estdetail_id;

thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question