3

How to select all field except any specific field in MySql.

i have a countries table and when i write query 

SELECT * FROM `countries` WHERE 1

it returns all record of countries table enter image description here

but i want all record except continent and i don't want to write all field manually in select box.

SELECT `id`, `iso`, `ioc`, `iso3`, `fips`, `name`, `continent`, `currency_code`, `currency_name`, `phone_prefix`, `postal_code`, `languages`, `geonameid` FROM `epoker_countries` WHERE 1
Ravi Singh
  • 2,042
  • 13
  • 29
Gagan
  • 573
  • 8
  • 25
  • see here http://stackoverflow.com/questions/14253994/selecting-all-fields-except-only-one-field-in-mysql] – PSR Jun 13 '13 at 05:59
  • see this http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql – Kevin Jun 13 '13 at 06:01
  • -1 shotgun tagging. If it's about MySQL, why's it tagged Oracle and PostgreSQL? This is also a bit of a frequently asked question; the fairly easily found answer is "there's no shorthand for this". – Craig Ringer Jun 13 '13 at 06:24
  • @CraigRinger Why do you discourage newbies by downvoting just because they've add a couple of extra tags? You are given the privilege to edit but you can't do that? – Kevin Jun 13 '13 at 06:28
  • @KevinPaladin: it should still be closed as a duplicate - especially because the duplicates **are** listed right next to the question without even searching for them. –  Jun 13 '13 at 07:02
  • @KevinPaladin A fair point; the fact that I'm getting really sick of people throwing in irrelevant tags doesn't mean I shouldn't *explain* why it's a problem and fix it. The main reason for the downvote is that it's an obvious FAQ, though again I should've also dup-voted it as such. – Craig Ringer Jun 13 '13 at 22:22

3 Answers3

2

SQL doesn't let you do that. But you can maybe make a view :

CREATE VIEW filter_countrie AS
SELECT `id`, `iso`, `ioc`, `iso3`, `fips`, `name`, `continent`, `currency_code`, 
       `currency_name`, `phone_prefix`, `postal_code`, `languages`, `geonameid` 
  FROM `countries` 
 WHERE 1

you'll then be able to query your view by doing

select * from filter_view
Patrice
  • 1,404
  • 1
  • 14
  • 27
1

You can't. Either specify the exact fieldnames, or specify the asterisk tot select all (which is not recommended).

For the record, these things are referred to as columns or fields. Records are the rows in the table.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
1

Unfortunately, it is either * or whatever fields you require. There is no *, (except col1, col2)

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284