Syntax error Using the value of an alias inside the same MySQL SELECT statement

Using the value of an alias inside the same MySQL SELECT statement

You cannot directly use an alias in the SELECT. Instead, use a user-defined variable. Following is the syntax. Here, @yourAliasName is our variable and alias −

select @yourAliasName :=curdate() as anyAliasName,concat(‘yourValue.',yourColumnName,' yourValue',@yourAliasName) as anyAliasName from yourTableName;

Let us first create a table −

mysql> create table DemoTable
(
   Name varchar(40)
);
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('John Smith');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('Chris Brown');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('David Miller');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('John Doe');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------------+
| Name         |
+--------------+
| John Smith   |
| Chris Brown  |
| David Miller |
| John Doe     |
+--------------+
4 rows in set (0.00 sec)

Following is the query to use the value of an alias inside the same SQL statement −

mysql> select @todayDate :=curdate() as todayDate,concat('Mr.',Name,' The current Date is=',@todayDate) as Result from DemoTable;

This will produce the following output −

+------------+------------------------------------------------+
| todayDate  | Result                                         |
+------------+------------------------------------------------+
| 2019-09-08 | Mr.John Smith The current Date is=2019-09-08   |
| 2019-09-08 | Mr.Chris Brown The current Date is=2019-09-08  |
| 2019-09-08 | Mr.David Miller The current Date is=2019-09-08 |
| 2019-09-08 | Mr.John Doe The current Date is=2019-09-08     |
+------------+------------------------------------------------+
4 rows in set (0.00 sec)
Updated on: 2019-10-07T12:37:47+05:30

824 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements