My current code is given below. I wanted to call all the columns from the table using * but the idcastncrew column name should display like castncrewid. In the requirement code, it's not working though, I wish there was a solution for my requirement such as the sample Requirement code.

Current code:-

SELECT idcastncrew AS castncrewid,castncrewname,castncrewtype,castncrewrole,imagelink,vendor,mode FROM subscriber;

Requirement :-

SELECT idcastncrew AS castncrewid, * FROM subscriber;

CodePudding user response：

You can alias columns when you alias the table, example as follows

MariaDB [DEV]> create table xxx (id int, str varchar(20));
MariaDB [DEV]> insert into xxx values (1, 'hi');
MariaDB [DEV]> insert into xxx values (2, 'Hello');
MariaDB [DEV]> insert into xxx values (3, 'World');
MariaDB [DEV]> insert into xxx values (4, 'Goodbye');

MariaDB [DEV]> select a.id as id1, a.* from xxx a order by 1;
 ------ ------ --------- 
| id1  | id   | str     |
 ------ ------ --------- 
|    1 |    1 | hi      |
|    2 |    2 | Hello   |
|    3 |    3 | World   |
|    4 |    4 | Goodbye |
 ------ ------ --------- 

I hope this helps...Rich

p.s. If this (or another) answer helps you, please take a moment to "accept" the answer that helped by clicking on the check mark beside the answer to toggle it from "greyed out" to "filled in".

CodePudding user response：

The closest I think you can get is to have the renamed column twice, once with the new name and once with the old name.

While MySQL does not allow * after an aliased column (causing your second code snippet to give an error), it does allow table.* anywhere...

SELECT idcastncrew AS castncrewid, subscriber.* FROM subscriber;

To re-itterate; you'll still get a idcastncrew column, but you will ALSO get a castncrewid column.

• There is no way to say don't include *this* column when using * in MySQL

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c69c537e46ad29e3c0c8c03d3ebd1bf7