----- Original Message ----- From: "Mark van 't Zet" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 20, 2004 6:59 AM Subject: Select * except certain columns
> Hello, > > I was wondering if it's possible to select all fields from a table except certain columns, e.g.: > > select * except text_name from text > > I have found several discussions on and outside the MySQL mailing lists that suggest it's not possible in (My)SQL. Why is this? It is a feature that would be very useful in several cases, such as: > > insert into text (select * except text_id, NULL as text_id from text) > select text.* except text_content, translatedText.text_content > from text, text as translatedText > where ... > > It would be very nice to have this in MySQL. > I agree that it would be nice to have this feature sometimes but I haven't seen it on any relational database I've ever seen. I'm guessing that it isn't in the SQL standards that most of the database companies use in developing their products. I would suggest two possible approaches, neither of which is likely to give you what you want any time soon: - lobby the relational database vendors to include this sort of syntax in the next version of the SQL standard - lobby one vendor, maybe MySQL, to support the syntax as an extension of the standard. If they implement it and it proves popular, maybe this will give the other vendors an incentive to include it as standard syntax in some future version of the standard Getting a single vendor to include the syntax as an extension might work relatively quickly - say, a year or two - but it might take a good bit longer before it is adopted by other vendors as a standard feature of SQL. Sorry, I'm sure that is not nearly as fast as you'd like to see this feature adopted but I think that is the only way you are likely to see it happen. The only approach that I can imagine that would be considerably quicker would be for you to find or write some kind of preprocessor that allows for this syntax; then, you could write "select * except colQ, colZ" and the preprocessor would turn it into "select colA, colB, colC, ... colP, colR, colS, ... colY". I've never tried to write a preprocessor so I can only begin to imagine how much work it might be to write, test, and implement. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
