29

here is what I'm trying to do- I have a table with lots of columns and want to create a view with one of the column reassigned based on certain combination of values in other columns, e.g.

Name, Age, Band, Alive ,,, <too many other fields)

And i want a query that will reassign one of the fields, e.g.

Select *, Age = 
CASE When "Name" = 'BRYAN ADAMS' AND "Alive" = 1 THEN 18
     ELSE "Age"
END
FROM Table

However, the schema that I now have is Name, Age, Band, Alive,,,,<too many>,, Age

I could use 'AS' in my select statment to make it Name, Age, Band, Alive,,,,<too many>,, Age_Computed.

However, I want to reach the original schema of Name, Age, Band, Alive.,,,, where Age is actually the computed age.

Is there a selective rename where I can do SELECT * and A_1 as A, B_1 as b? (and then A_1 completely disappears) or a selective * where I can select all but certain columns? (which would also solve the question asked in the previous statement)

I know the hacky way where I enumerate all columns and create an appropriate query, but I'm still hopeful there is a 'simpler' way to do this.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0fnt
  • 8,211
  • 9
  • 45
  • 62
  • 5
    select * not a great idea. Just list all the columns. Select * is for people typing at a prompt, not for efficient programs. – bmargulies Dec 25 '12 at 13:53
  • Thanks for the prompt reply, but I actually need all the columns, and don't want to enumerate them by getting the table schema and building a query. – 0fnt Dec 25 '12 at 13:54
  • 2
    So list all the columns. SQL syntax forbids combining * with anything else. – bmargulies Dec 25 '12 at 13:55
  • Thanks and thanks for the point about efficiency. I'm aware of the way that requires listing of columns, I'm curious if there is any other way this can be done. In fact, may I humbly request posters to not answer the way of 'listing columns'. As I indicated in the question, I am already aware of it and am exploring if there exists an alternate way. – 0fnt Dec 25 '12 at 14:00
  • 2
    [Bad habits to kick: using SELECT * / omitting the column list](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list) – marc_s Dec 25 '12 at 14:01
  • 2
    *There is no alternative*. SQL doesn't have one. You'll have to write a program that reads out the metadata and generates your create view. – bmargulies Dec 25 '12 at 14:01
  • Thanks bmarguiles and marc_s. bmarguiles, can you please put that as answer and I will mark the question answered. – 0fnt Dec 25 '12 at 14:21
  • @bmargulies Now we have `SELECT * REPLACE` – Lukasz Szozda Sep 09 '18 at 11:23
  • 1
    There is also this answer: https://stackoverflow.com/a/41635402/6032037 but it includes both the old column name and the new on the result set. – gmelodie Jun 09 '22 at 12:48

3 Answers3

15

Sorry, no, there is not a way to replace an existing column name using a SELECT * construct as you desire.

It is always better to define columns explicitly, especially for views, and never use SELECT *. Just use the table's DDL as a model when you create the view. That way you can alter any column definition you want (as in your question) and eliminate columns inappropriate for the view. We use this technique to mask or eliminate columns containing sensitive data like social security numbers and passwords. The link provided by marc_s in the comments is a good read.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • 5
    Not always better, because the structure of the table might change and this might be hidden for the developer, especially in places where you cannot use ORM and you need native query . – makkasi Feb 07 '19 at 08:57
7

Google BigQuery supports SELECT * REPLACE:

A SELECT * REPLACE statement specifies one or more expression AS identifier clauses. Each identifier must match a column name from the SELECT * statement.

In the output column list, the column that matches the identifier in a REPLACE clause is replaced by the expression in that REPLACE clause.

A SELECT * REPLACE statement does not change the names or order of columns. However, it can change the value and the value type.

Select *, Age = CASE When "Name" = 'BRYAN ADAMS' AND "Alive" = 1 THEN 18
                     ELSE "Age"
                END
FROM tab
=>
SELECT * REPLACE(CASE WHEN Name = 'BRYAN ADAMS' AND Alive = 1 THEN 18
                      ELSE Age END AS Age)
FROM Tab
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
2

Actually, there is a way to do this in MySQL. You need to use a hack to select all but one column as posted here, then add it separately in the AS statement.

Here is an example:

-- Set-up some example data
DROP TABLE IF EXISTS test;
CREATE TABLE `test` (`ID` int(2), `date` datetime, `val0` varchar(1), val1 INT(1), val2 INT(4), PRIMARY KEY(ID, `date`));
INSERT INTO `test` (`ID`, `date`, `val0`, `val1`, `val2`) VALUES
(1, '2016-03-07 12:20:00', 'a', 1, 1001),
(1, '2016-04-02 12:20:00', 'b', 2, 1004),
(1, '2016-03-01 10:09:00', 'c', 3, 1009),
(1, '2015-04-12 10:09:00', 'd', 4, 1016),
(1, '2016-03-03 12:20:00', 'e', 5, 1025);

-- Select all columns, renaming 'val0' as 'yabadabadoo':
SET @s = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'val0,', '') 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test' AND TABLE_SCHEMA = 
  '<database_name>'), ', val0 AS `yabadabadoo` FROM test');

PREPARE stmt1 FROM @s;
EXECUTE stmt1;
Michal J Figurski
  • 1,262
  • 1
  • 11
  • 18