Query Syntax

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;
hdcowboyazAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
Why do you need the order by?
0
 
lwadwellCommented:
Your SELECT does not return estdetail_id ... which is needed for the join "WHERE z.estdetail_id = d.estdetail_id".
0
 
lwadwellCommented:
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
 
hdcowboyazAuthor Commented:
i get the same error

Error Code : 1221
Incorrect usage of UPDATE and ORDER BY
0
 
hdcowboyazAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.