Error: Every derived table must have its own alias

Question

UPDATE `FlightSchedule`
SET delay=(
  SELECT *
  FROM (
    SELECT
      MINUTE(ETA - STA)
    FROM `FlightSchedule`
      WHERE `flightNum_arr` = '3517'
  )
)
WHERE `flightNum_arr` = '3517';

Says:

"Every derived table must have its own alias".

How to fix this issue?

Alma Do · Accepted Answer · 2013-08-26T13:29:59.410

3

Fixing it - exactly like it was shown in your error message:

UPDATE `FlightSchedule` 
SET delay=
(SELECT update_field
 FROM 
 (
  SELECT MINUTE (ETA - STA) AS update_field
  FROM `FlightSchedule` 
  WHERE `flightNum_arr`='3517'
 ) AS internal_0
)
WHERE `flightNum_arr`='3517';

But actually above there more correct suggestion - get rid of that nested subquery at all (see Gordon's answer).

Edit (based on comments):

If you want to find difference, use TIMEDIFF function:

UPDATE `FlightSchedule` 
    SET delay = TIMEDIFF(ETA - STA)
    WHERE `flightNum_arr`='3517';

edited Aug 26 '13 at 13:29

answered Aug 26 '13 at 12:59

Alma Do

37,009
9
76
105

+1 for such a nice explanation, lol :) – Hanky Panky Aug 26 '13 at 13:01
@ØHankyPankyØ - I think a lot of problems will be solved if people will not just notice error messages, but actually _read_ them and **understand** :p – Alma Do Aug 26 '13 at 13:02
Actually, the answer is syntactically incorrect, because the second alias is not needed and not appropriate. – Gordon Linoff Aug 26 '13 at 13:03
@GordonLinoff - yes, thanks (misplaced those brackets) – Alma Do Aug 26 '13 at 13:04
Now it says Truncated incorrect time value: '6100.000000' – Klausos Klausos Aug 26 '13 at 13:09
@KlausosKlausos - that's a matter of logic, not a syntax. – Alma Do Aug 26 '13 at 13:10
@Alma Do Mundo: The goal is just to calculate the value of delay for a given field specified by `flightNum_arr`='3517'. – Klausos Klausos Aug 26 '13 at 13:13
Now it says Truncated incorrect time value: '7400.000000'. The field delay is varchar. I don't understand why Minute(ETA-STA) provides 7400, though it must be equal to 7 minutes. – Klausos Klausos Aug 26 '13 at 13:26
STA: 2013-08-26 20:30:00 ETA: 2013-08-26 21:04:00 – Klausos Klausos Aug 26 '13 at 13:29
@KlausosKlausos - see my edit for that issue – Alma Do Aug 26 '13 at 13:30
Based on your last update I came to the following solution: UPDATE `FlightSchedule` SET delay = MINUTE(TIMEDIFF(ETA, STA)) WHERE `flightNum_arr`='3729'; – Klausos Klausos Aug 26 '13 at 13:34

Gordon Linoff · Answer 2 · 2013-08-26T13:10:02.417

3

The problem is in the nested subquery. But you don't even need it. The right way to write this query is:

UPDATE `FlightSchedule` 
    SET delay = MINUTE(ETA - STA)
    WHERE `flightNum_arr`='3517';

edited Aug 26 '13 at 13:10

answered Aug 26 '13 at 13:02

Gordon Linoff

1,242,037
58
646
786

You can't specify target table 'FlightSchedule' for update in FROM clause – Klausos Klausos Aug 26 '13 at 13:03
@KlausosKlausos . . . Sorry, that was one step in my reasoning. – Gordon Linoff Aug 26 '13 at 13:04
I need to use MINUTE (ETA - STA). Could you please tell me how to use it in your solution? – Klausos Klausos Aug 26 '13 at 13:07

Error: Every derived table must have its own alias

2 Answers2