Mysql Subquery Syntax
I am wondering why expressions select * as t
like below appear in mysql subqueries .
The following removes the oldest 3 rows in the table according to the column created_time
.
Why is it right
DELETE FROM mytable WHERE id = ANY
( SELECT * FROM ( SELECT id FROM mytable ORDER BY created_time ASC LIMIT 3')as t)
but not
DELETE FROM mytable WHERE id = ANY
(SELECT id FROM mytable ORDER BY created_time ASC LIMIT 3)
?
The second form makes sense to me. This does not work and I would like to understand why the former is necessary. In particular, what is t
and what does it do as t
?
source to share
In many databases, a subquery in a clause from
must have an explicit alias. as
is optional. I usually use as
for columns and leave it for tables:
DELETE FROM mytable
WHERE id = ANY ( SELECT * FROM ( SELECT id FROM mytable ORDER BY created_time ASC LIMIT 3') t)
Why you need a subquery is a MySQL whim. This prevents the table reference from being referenced in delete
or update
in a subquery clause. Oh, this allows it in the subquery-in-the-subquery clause. Thus, it is quite easy to get around this limitation.
source to share