CODEWITHSUNDEEP
mysqlsqlsyntaxsubquery
rocketas
rocketas

Reputation: 1707

Mysql Subquery Syntax

I'm wondering why statements like select * as t appear in mysql subqueries like the following.

The following deletes the oldest 3 rows in a table according to a created_time column.

Why is this right

DELETE FROM mytable WHERE id = ANY 
( SELECT * FROM ( SELECT id FROM mytable ORDER BY created_time ASC LIMIT 3')as t)

and not

DELETE FROM mytable WHERE id = ANY 
(SELECT id FROM mytable ORDER BY created_time ASC LIMIT 3)

?

To me, the second form makes sense. It doesn't work and I'd like to understand why the first is necessary. Specifically, what is t and what does as t do?

Upvotes: 3

Views: 474

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In many databases, a subquery in a from clause needs to have an explicit alias. The as is optional. I typically use as for columns and leave it out 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 the subquery is a vagary of MySQL. It doesn't allow the table reference in a delete or update to appear in a subquery clause. Oh, it does allow it in a subquery-within-a-subquery clause. So, it is pretty easy to work around this limitation.

Upvotes: 3

Related Questions