SELECT all (but one column as null)

Question

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!

Solution 1

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

Solution 2

SELECT NULL AS ID, Column1, Column2, ..., ColumnN
    FROM my_table

Solution 3

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;

Solution 4

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).

View more solutions