Bug #3275 INSERT INTO table1 AS alias1 - to easier use of LOCK TABLES table1 AS alias1
Submitted: 23 Mar 2004 14:14 Modified: 4 Feb 2009 14:24
Reporter: Ondra Zizka Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: ufr

[23 Mar 2004 14:14] Ondra Zizka
I suggest to add a feature to INSERT INTO, to mark the table with an alias.

  INSERT INTO table1 AS alias1 ...

This will be useful together with LOCK TABLES table1 AS alias1.
In my apps, I always use aliases in the form
  "SELECT cust.id, cust.name, cust.address, ... FROM %s AS cust",
because the table names can change over time and they are long. When I insert to them, I do
  "INSERT INTO %s ...".
But when I want to lock the table, I have to explicitely name it both with and without the alias:
   sprintf(str, "LOCK TABLES %s AS cust WRITE, %s WRITE", sTableName, sTableName);
(Of course I do not use sprintf, but more complicated tool.)

Thanks, Ondra

How to repeat:
Just think about it :)
LOCK TABLES very_very_very_very_very_long_name_table1 AS alias1 WRITE;
SELECT alias.id, alias.name, alias.adress, IF(alias.x < alias.y, alias.x+alias.y, alias.y), ...
FROM  very_very_very_very_very_long_name_table1 AS alias1 LEFT JOIN ... ;
INSERT INTO very_very_very_very_very_long_name_table1 AS alias1;

Currently, the table has to be locked twice:
LOCK TABLES very_very_very_very_very_long_name_table1 AS alias1 WRITE, very_very_very_very_very_long_name_table1 WRITE;

Suggested fix:
Add the [AS alias] section into INSERT --->
INSERT INTO table_name [AS alias] VALUES ...
[4 Feb 2009 14:24] Susanne Ebrecht
Many thanks for writing a feature request. Our development will think about it.