8

in mysql, is it possible to get all columns and data from a table, but select the ID column as NULL?

Something like

SELECT *, id AS (SELECT '') FROM my_table WHERE 1

I want to pull in the ID column, so it doesn't mess up the column count for a later data load, but I want to eliminate the column's value so it doesn't cause key conflicts.

Thanks!

julio
  • 6,630
  • 15
  • 60
  • 82
  • Sorry, I should specify a little more clearly-- the table has probably 80 columns-- I'd much prefer to use the * notation rather than having to specify the whole column list. Thanks – julio May 16 '11 at 21:44
  • +1, possible duplicate of [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) Well, the linked question is more general (not bound to mysql) but the answers are also applicable here. – Tomas Apr 09 '14 at 07:20

7 Answers7

9

So, there is no way to use SELECT * and replace one of the columns with another value.

If you don't want to use the select statement provided in your application code, you can create a view with those fields populated and the ID nulled out. You'll still have to name all the columns at least once.

select NULL as ID, t.col2, t.col3, t.col4 from mytable t where col2 = 1

Here is the easy way to get all of your column names:

SELECT column_name FROM information_schema.columns WHERE table_name = mytable
Vinnie
  • 3,889
  • 1
  • 26
  • 29
5
SELECT NULL AS ID, Column1, Column2, ..., ColumnN
    FROM my_table
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

What about

SELECT *, NULL AS id FROM my_table

You'd get id listed twice, so you need to expand the * to all the column names, for each table you want this to run on.

(If you want column names to be extracted automatically, you can probably use vendor-specific functions; I can't remember if MySQL has any for this situation).

csl
  • 10,937
  • 5
  • 57
  • 89
1

Since it sounds like this is preparatory to a data dump anyway, use a temp table

CREATE TEMP TABLE t000 AS SELECT * FROM my_table; -- or INSERT INTO
UPDATE t000 SET id = NULL;
Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53
0

You can't do that. This is not allowed in SQL. You should write all fields and then say null for id.

SELECT a,b,c,d,e, id as NULL from table

ahmet alp balkan
  • 42,679
  • 38
  • 138
  • 214
0

You can do something like:

SELECT null AS id, t.* FROM my_table t

But it will include the id twice. You'll need to specify the columns if you only want the null id column.

SELECT null As id, t.col1, t.col2 FROM my_table t
Sam
  • 9,933
  • 12
  • 68
  • 104
0

The truly relational language Tutorial D allows a projection (all other other relevant relational operators) to be expressed in terms of the attributes to be removed instead of the ones to be kept e.g. 

my_relvar { ALL BUT ID }

(but being truly relational Tutorial D of course doesn't have a concept of null!)

Sadly, SQL does not have such a language feature and you must write in longhand all the column names.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138