Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL SELECT only 1 field with AS alias

I'd like to select all columns in a table, but give only one of them an alias.

I have this table:

id
base
window
thickness
shading
shape
... and many more columns

Now, I want to select the base with an alias, like so: SELECT base AS structure. But I want to select all the other fields as well, without having to type them all out.

I tried SELECT *, base AS structure, but it gives me an error.

In truth, that's not even really what I want, because I don't want base to show up at all.

Is this at all possible?

like image 652
John MacArthur Avatar asked Aug 02 '11 01:08

John MacArthur


2 Answers

No, it isn't. Suck it up and type them all out :)

like image 164
Tom Studee Avatar answered Oct 13 '22 17:10

Tom Studee


No.

You either list the ones you want, or you say "all" by writing *.

These are the two options at your disposal.

Laziness: begone! (And, let's face it, if you really need this alias, then your field is probably named wrong in the first place...)


Ultimately, you could create a VIEW to do this job transparently, but then you'd have to keep updating it as you ALTER your original table.


I was trying to avoid bringing this to your attention, but this answer does demonstrate a rounadabout way:

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_exclude>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

(Replacing <table>, <database> and <columns_to_exclude>).

I wish to re-iterate, though: don't. Something's wrong if you have to do this.

like image 26
Lightness Races in Orbit Avatar answered Oct 13 '22 18:10

Lightness Races in Orbit