Can't use variable as table alias

[Luciano Fantuzzi]

I'm having problems when trying to pass a variable as a table alias during a query. For example:

-----------------------------------------------------------
mysql> SET @a:="name";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "peter" AS @a FROM DUAL;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near
'@a FROM DUAL' at line 1
-----------------------------------------------------------

The answer I got was:

To interpolate a variable into a statement in this way, you need to
use a prepared statement for something like this:

-----------------------------------------------------------
mysql> SET @n = "name";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT \"Peter\" AS ", @n, " FROM DUAL");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+-------+
| name |
+-------+
| Peter |
+-------+
1 row in set (0.00 sec)
-----------------------------------------------------------

OK, here is my real case where I need a dynamic alias (I suppose) to avoid the "ERROR 1248 (42000): Every derived table must have its own alias" and I can't use the method above because of the type of query (UPDATE multirow).

-----------------------------------------------------------
mysql> SELECT * FROM sis_estad_regs LIMIT 3;
+----+-----------+----------+
| id | tabla | cantidad |
+----+-----------+----------+
| 1 | archivos | 0 |
| 2 | barrios | 0 |
| 3 | catalogos | 0 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> SET @c:=1;
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE sis_estad_regs t1 SET t1.cantidad=(
-> SELECT COUNT(*) FROM (
-> SELECT t2.tabla FROM sis_estad_regs t2 WHERE t2.id=(
-> SELECT @c:=@c+1)));
ERROR 1248 (42000): Every derived table must have its own alias

-----------------------------------------------------------

The first table contains the names from other tables in the same database. The target of the query is to count and update 'cantidad' with the total of rows from each table. I'd prefer to not use several querys, specially involving WHILE, REPEAT or flow control statements (always when it be possible).
And the problem is that I don't know how to put a dynamic alias (at the end of the first from, I think) to avoid the error. Some idea or advice? Thanks!



Edited 2 time(s). Last edit at 04/02/2007 09:05AM by Luciano Fantuzzi.