Can I use aliases in an INSERT statement?

Question

Can we use aliases with insert into syntax?

None of the following work:

INSERT INTO tableblabla AS bla
INSERT INTO bla tableblabla
INSERT INTO tableblabla bla

I can't seem to find any information about this; is there a valid way to use aliases in an INSERT statement?

About the possible reasons: http://bugs.mysql.com/bug.php?id=3275

Answer 1

Alas, it's not possible as can be seen by the INSERT syntax. Note how it just says [INTO] tbl_name with no mention of [AS] alias, whereas the Update syntax, which does allow aliases, says "table_reference" and further documentation states this can expand to table_factor which includes tbl_name [AS] alias.

It's unfortunate because having the ability to use table aliases on INSERTs would be very useful for INSERT ... ON DUPLICATE KEY UPDATE statements. Especially when performing checks on the columns like in this example:

insert into very_long_table_name_that_can_be_modified_one_day_or_during_testing (
    mykey, 
    column1,
    column2,
    column3,
    <column99>
)
select
    mykey, 
    column1,
    column2,
    column3,
    <column99>
from subquery
on duplicate key update 
    column1 = ifnull(values(column1), very_long_table_name_that_can_be_modified_one_day_or_during_testing.column1), 
    column2 = ifnull(values(column2), very_long_table_name_that_can_be_modified_one_day_or_during_testing.column2), 
    column3 = ifnull(values(column3), very_long_table_name_that_can_be_modified_one_day_or_during_testing.column3), 
    <column99>;

If the table name changes, one has to modify lots of lines of code compared to just having an alias at the beginning which is used in the query.