Re: update and select question

[Jigal van Hemert]

From: "黄高峰"

> UPDATE
>     NGLDENHDT
> SET
>     EDT_HUK_FLG = :EDT_HUK_FLG
> WHERE
>   KAI_CDE = :KAI_CDE
>   AND EDT_NUM = (SELECT MAX(EDT_NUM)
>                                FROM NGLDENHDT
>                                WHERE KAI_CDE = :KAI_CDE_T1
>                                AND EDT_NUM != '9999');
>
> the error message was:
>
>             You can't specify target table 'NGLDENHDT' for update in FROM

For a small table you could use:

UPDATE
    NGLDENHDT
SET
    EDT_HUK_FLG = :EDT_HUK_FLG
WHERE
  KAI_CDE = :KAI_CDE
  AND EDT_NUM != '9999')
ORDER BY
  EDT_NUM DESC
LIMIT 1;

This would sort the table on EDT_NUM with the biggest number first and then
only update a single record.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]