MySQL: Select all columns EXCEPT specified column
This returns all columns:
SELECT * FROM tablexyzSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I like the idea of using a temporary table however the code provided for the temporary table does not work.
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.
Works with me. I again tried it.
Select *
into #testx
from tablename
alter table #testx drop column joindate
Select * from #testx
Select *
into #testx
from tablename
alter table #testx drop column joindate
Select * from #testx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
Then the following should give you your result set:
CALL spTest('MyTable,'foobar');
For example:
CREATE DEFINER=`root`@`localhost`
BEGIN
SET @strSQL= (SELECT GROUP_CONCAT(column_name) FROM INFORMATION_SCHEMA.COLUMNS
SET @strSQL=CONCAT(' SELECT ',@strSQL,' FROM ',pTable,' LIMIT 1000;');
PREPARE stmt FROM @strSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
BEGIN
SET @strSQL= (SELECT GROUP_CONCAT(column_name) FROM INFORMATION_SCHEMA.COLUMNS
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');
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.
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.
Select *
into #temp
from table
alter table #temp drop column column_name
Select *
from #temp