Selecting All Fields Except Only One Field in MySQL

Select all columns except one in MySQL?

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') 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_omit>

Selecting all fields except only one field in mysql

you can do it easily like that

lets say your field is an id = 5

then

   select * from your_table where id !=5 

and if you mean columns

lets say you dont want select column3

then

   select column1,column2,column4 from tablename;

if you have many columns

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

    PREPARE stmt1 FROM @sql;
   EXECUTE stmt1;

How to select all the columns of a table except one column?

You can use this approach to get the data from all the columns except one:-

1. Insert all the data into a temporary table
2. Then drop the column which you dont want from the temporary table
3. Fetch the data from the temporary table(This will not contain the data of the removed column)
4. Drop the temporary table

Something like this:

SELECT * INTO #TemporaryTable FROM YourTableName

ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove

SELECT * FROM #TemporaryTable 

DROP TABLE #TemporaryTable 

How to select all columns in sql except one column?

declare @cols varchar(max), @sql varchar(max)
SELECT  @cols = STUFF
    (
        ( 
            SELECT DISTINCT '], [' + name
            FROM sys.columns
            where object_id = (
                select top 1 object_id from sys.objects
                where name = 'TBLUser'
            )
            and name not in ('age')
            FOR XML PATH('')
        ), 1, 2, ''
    ) + ']'
select @sql = 'select ' + @cols + ' from TBLUser'  
exec (@sql)

Select all columns except one column in CakePHP?

I doubt you will gain much by excluding the column from your query. If you really need to do this then you can get the model's schema and then remove the column from that and then use the remaining columns in your find() by defining the fields you want returned by the query:-

// Get the model’s schema.
$schema = $this->Order->schema();
// Remove the `attribute` column from the schema.
unset($schema['attribute']);
// Determine the remaining columns from the schema.
$cols = array_keys($schema);

// Now call your query specifying the fields you want back using the 
// columns we’ve just determined.
$data = $this->Order->find('first', 
    array(
        'fields' => $cols,
        'conditions' => array('Order.id' => 1)
    )
);

Select all columns except one in MySQL?

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') 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_omit>

MySQL: Selecting all columns in a table plus one column from the same table

You have to give the table name in your query, otherwise mysql complains :

SELECT column20, mytable.* FROM mytable

PS: I have absolutely no idea as to why, because SELECT *, column20 FROM mytable works just fine... Strange things happens sometimes ^^

---

Related Topics