Update Table with query in Where Clause

Question

What is that I am doing wrong with the below query

Error Message :  You can't specify target table 'cp_asseteventinquiry' for update in FROM clause

Query :

UPDATE cp_asseteventinquiry
SET Due_Date= curdate()
WHERE TopLevelAsset_Num =
(
   SELECT Distinct(TopLevelAsset_Num) FROM cp_asseteventinquiry
   WHERE cp_asseteventinquiry.History='NO'
   AND NOT EXISTS(SELECT Scheduled_Date FROM flightlogs
   WHERE flightlogs.Asset=cp_asseteventinquiry.TopLevelAsset_Num 
   AND flightlogs.Scheduled_Date=date_format (curdate(), '%m/%d/%Y'))
);

Didn't you ask the same question yesterday? – spencer7593 Jan 15 '15 at 20:49 — spencer7593, Jan 15 '15 at 20:49

potashin · Accepted Answer · 2015-01-15T20:58:00.423

0

Just wrap your inner query with SELECT like this :

UPDATE cp_asseteventinquiry
SET Due_Date= curdate()
WHERE TopLevelAsset_Num = (
    SELECT * 
    FROM ( 
          SELECT Distinct(TopLevelAsset_Num)
          FROM cp_asseteventinquiry
          WHERE cp_asseteventinquiry.History='NO'
            AND NOT EXISTS( SELECT Scheduled_Date 
                            FROM flightlogs 
                            WHERE flightlogs.Asset=cp_asseteventinquiry.TopLevelAsset_Num
                              AND flightlogs.Scheduled_Date=date_format (curdate(), '%m/%d/%Y')
            )
    ) AS t)

edited Jan 15 '15 at 20:58

answered Jan 15 '15 at 20:46

potashin

44,205
11
83
107

Thanks for the Reply Notulysses, But im just confused as the query i posted is wrapped in () – Learning Bee Jan 15 '15 at 20:54
Am i Missing something else ?? – Learning Bee Jan 15 '15 at 20:54
1

Thanks Much Notulysses – Learning Bee Jan 15 '15 at 21:34

Update Table with query in Where Clause

1 Answers1