CODEWITHSUNDEEP
mysqlinsertalias
MEM
MEM

Reputation: 31347

Can I use aliases in an INSERT statement?

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

Upvotes: 9

Views: 17044

Answers (2)

cdesnoye
cdesnoye

Reputation: 231

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.

Upvotes: 23

Daniel Vandersluis
Daniel Vandersluis

Reputation: 94253

The INSERT syntax doesn't allow for aliases. Why would you need one in an INSERT statement anyways? You can only INSERT into one table at a time.

Upvotes: 8

Related Questions