Solved

MySQL: Select all columns EXCEPT specified column

Posted on 2014-01-23
10
13,353 Views
Last Modified: 2014-01-25
This returns all columns:
SELECT * FROM tablexyz

Open in new window

I want all columns EXCEPT the column named foobar to be selected.
0
Comment
Question by:hankknight
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 33

Assisted Solution

by:snoyes_jw
snoyes_jw earned 50 total points
ID: 39804632
There is no syntax for that. You have to do:
SELECT all, the, other, columns FROM table;

Open in new window

0
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39804639
could be like this

Select *
into #temp
from table

alter table #temp drop column column_name

Select *
from #temp
0
 
LVL 16

Author Comment

by:hankknight
ID: 39804867
I like the idea of using a temporary table however the code provided for the temporary table does not work.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 24

Expert Comment

by:johanntagle
ID: 39805492
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
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39805724
Works with me.  I again tried it.

Select *
into #testx
from tablename

alter table #testx drop column joindate

Select * from #testx
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 150 total points
ID: 39805734
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 300 total points
ID: 39806524
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
 
LVL 1

Expert Comment

by:Multimatic
ID: 39806572
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 39807740
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39808945
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Importing and exporting data Magento 1.x ? 4 87
Clean text to insert in database 9 65
selecting date modified field from a table 2 45
setup wamp server for first time 2 74
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question