Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 18288
  • Last Modified:

MySQL: Select all columns EXCEPT specified column

This returns all columns:
SELECT * FROM tablexyz

Open in new window

I want all columns EXCEPT the column named foobar to be selected.
0
hankknight
Asked:
hankknight
  • 3
  • 2
  • 2
  • +3
3 Solutions
 
snoyes_jwCommented:
There is no syntax for that. You have to do:
SELECT all, the, other, columns FROM table;

Open in new window

0
 
ravikantninaveCommented:
could be like this

Select *
into #temp
from table

alter table #temp drop column column_name

Select *
from #temp
0
 
hankknightAuthor Commented:
I like the idea of using a temporary table however the code provided for the temporary table does not work.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
johanntagleCommented:
Are you open to using a stored procedure?  I think it can be done using dynamically formed SQL within a stored procedure, but I won't put effort into it if this is not an option for you.
0
 
ravikantninaveCommented:
Works with me.  I again tried it.

Select *
into #testx
from tablename

alter table #testx drop column joindate

Select * from #testx
0
 
johanntagleCommented:
Yeah that should work, but how big is the data and how frequent will this be done?  I'm under the impression this will be done frequently so copying the full table and dropping the column will be quite inefficient.
0
 
Kevin CrossChief Technology OfficerCommented:
Silly question, but this is MySQL, correct?
If yes, then "SELECT ... INTO #temp_table" syntax does not work that I am aware of.
You have to CREATE TEMPORARY TABLE

For example:
-- create temporary copy of data
CREATE TEMPORARY TABLE temp_table
SELECT *
FROM orig_table
;

-- drop unnecessary column
ALTER TABLE temp_table
DROP COLUMN `column_to_exclude`
;

-- select data from temp
SELECT *
FROM temp_table
;

Open in new window


However, I agree with Johann.  Is it not possible to create a view one time in which you explicitly list the columns you want except the one?  From that point forward, you can SELECT * FROM view_name.
0
 
MultimaticCommented:
Needed to do similar before and used the schema. Originally done on business layer and built dynamic SQL there, but can be done easily in stored proc using prepared statement.
For example:

CREATE DEFINER=`root`@`localhost` PROCEDURE `procTest`(pTable VARCHAR(30),pExcludeField VARCHAR(30))
BEGIN
      SET @strSQL= (SELECT GROUP_CONCAT(column_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=pTable AND column_name<>pExcludeField);
      SET  @strSQL=CONCAT(' SELECT  ',@strSQL,' FROM ',pTable,' LIMIT 1000;');
      PREPARE stmt FROM @strSQL;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

Then the following should give you your result set:

CALL spTest('MyTable,'foobar');
0
 
johanntagleCommented:
Multimatic's solution was what I had in mind when I said use a stored procedure.  This would have been the more elegant and efficient way to do it.
0
 
Kevin CrossChief Technology OfficerCommented:
Again, I agree.  I posted to correct the dialog that the #temp_table syntax was correct as I expect the Experts did not see the topic area.  I love the use of dynamic SQL and the INFORMATION_SCHEMA for this.  I have similar situations on Microsoft SQL as timestamp columns cannot INSERT; therefore, when copying data across like systems, you have to select every column but the timestamp.  A technique like this is useful for that, although on the SQL side I just leverage the GUI's ability to generate a SELECT with all the columns then I manually remove the one.  The new MySQL Workbench can do this also, so I guess that is another option.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now