select all but use newname for some fields while selecting all data

Question

I want to use newname on selected fields while selecting all fields.

ie

SELECT id NEWNAME uid all fields form table;

is there anyway for me to do this, since the table has a lot of fields i was looking for a shorter version. or i could always get the fields of the table implode them in a string replace the field with field NEWNAME new_field but that is not short at all. so any suggestions?

thank you

You can do `SELECT *, id NEWNAME, othercol OTHERNAME` which will give you _duplicate_ columns, one of which has your `NEWNAME` alias, but it is always recommended to be explicit in the `SELECT` list anyway, and not `SELECT *` in production code. — Michael Berkowski, May 20 '13 at 15:37
so that will give me id and its newname? so can i just use exemption in the query to not include the oldnames? — magicianiam, May 20 '13 at 15:40
@AbsoluteƵERØ Normalization doesn't seem to be relevant to this question. Unless you think the reason it has so many fields is because normalization would move many columns into another table. — Barmar, May 20 '13 at 15:49
@Barmar I guess I'm not seeing the OP's reason for pulling the ID twice unless there were duplicate columns? — AbsoluteƵERØ, May 20 '13 at 15:52
@AbsoluteƵERØ He doesn't want to pull it twice, he wants to pull all fields but give this field an alias. — Barmar, May 20 '13 at 15:53
http://stackoverflow.com/questions/14253994/selecting-all-fields-except-only-one-field-in-mysql - exemption but I dont think i can apply it there. I dont plan on pulling ID twice i want to rename id to uid to meet a certain specification, and since the table is also used else where it should retain its current column names. — magicianiam, May 20 '13 at 15:59

score 1 · Answer 1 · edited May 20 '13 at 15:50

1

You can manage to select all COLUMS from a table simply using * and selecting id with an ALIAS

SELECT *, id as NEWNAME from table;

edited May 20 '13 at 15:50

Barmar

741,623
53
500
612

answered May 20 '13 at 15:38

Fabio

23,183
12
55
64

1

To as the OP called it *"exempt"* old names they would have to list every column with it's new name and not use the `*` column match for all columns. – AbsoluteƵERØ May 20 '13 at 15:53

AbsoluteƵERØ · Accepted Answer · 2013-05-20T16:20:29.980

1

From the MySQL SELECT reference:

A select_expr can be given an alias using AS *alias_name*. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

In operation

To do this you need to use an alias and specifically call every table column that you would need to pull. But NOT use the * column match for all columns. An example might be:

SELECT id as user_id,name as user_name,
st_code as state,postal as zipcode from table

If you were pulling from two different tables by had duplicate column names you would do something like:

SELECT a.id as user_id,b.id as parent_id
from table_1 a,table_2 b where b.name=a.name;

edited May 20 '13 at 16:20

answered May 20 '13 at 16:15

AbsoluteƵERØ

7,816
2
24
35

from the recent comments this will also return both id and user_id, am i correct? – magicianiam May 20 '13 at 20:55
@magicianlam if you have two different columns, one id and one user_id they would only be returned if you requested them. – AbsoluteƵERØ May 21 '13 at 02:48
is it possible not to include id in the result query? as to avoid duplicate entries. thank you – magicianiam May 21 '13 at 21:41
You can include it as many times as you want. More than once and it might be confusing on the programming side. – AbsoluteƵERØ May 21 '13 at 23:16

score 0 · Answer 3 · answered May 20 '13 at 15:39

0

You can use the following method:

select a.id as newname, a.* from tablename a

while the disadvantage is id is repeated as well in the selected columns.

answered May 20 '13 at 15:39

Nagarjun

2,346
19
28

then can i just use an exemption to not include id? – magicianiam May 20 '13 at 15:42
1

You can't add any exempts there. a.* will include all fields. you will have an additional field which you can ignore while processing the result set. if you don't want any additional fields, there is no way except you explicitly specify what fields you want. like select a.id, a.title, a.desc from tablename a – Nagarjun May 20 '13 at 15:49
yes but then the id given a new name will also be called, so in my query i will now have id-"old" and id-"new". correct me if im wrong. – magicianiam May 20 '13 at 16:01
Yes, you will have both fields in your result set. – Nagarjun May 20 '13 at 17:43
i also want to exclude the old id from the result – magicianiam May 20 '13 at 17:52

select all but use newname for some fields while selecting all data

3 Answers3

In operation