asked on

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;

Lee Wadwell

Your SELECT does not return estdetail_id ... which is needed for the join "WHERE z.estdetail_id = d.estdetail_id".

Lee Wadwell

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;

hdcowboyaz

ASKER

i get the same error

Error Code : 1221
Incorrect usage of UPDATE and ORDER BY

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

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

Start Free Trial

hdcowboyaz

ASKER

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