0

How can I select all columns except the primary key from a table? It should be noted that I don't know field names in this table, the operation should be able to run automatically.

Thank you for the help.

Pratik Joshi
  • 11,485
  • 7
  • 41
  • 73
Behzad Azizan
  • 33
  • 2
  • 7
  • 2
    Can I ask out of curiosity why you would want to select everything BUT the primary key? – AdamMc331 Oct 27 '14 at 05:44
  • Please show your table definition. You can use `DESC ` to obtain the definition. – davidkonrad Oct 27 '14 at 05:44
  • 1
    Possible Duplicate Of http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql – AdamMc331 Oct 27 '14 at 05:47
  • If you know at least the names of the primary key columns, the linked duplicate seems helpful. – Thilo Oct 27 '14 at 05:52
  • @Thilo thanks for pointing that out. I was not 100% sure if that was a duplicate question, or enough to answer OP's so I just left a comment rather than flag it as duplicate. – AdamMc331 Oct 27 '14 at 05:55
  • possible duplicate of [how to select all columns without PRIMARY key in mysql?](http://stackoverflow.com/questions/26550801/how-to-select-all-columns-without-primary-key-in-mysql) – Strawberry Oct 27 '14 at 07:51

1 Answers1

-1

Use:

To get All columns Without primary use

 SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE (`TABLE_SCHEMA` = 'wpresstest') AND (`TABLE_NAME` = 'wp_countries') AND (`COLUMN_KEY` <> 'PRI')

See you cant do all using simple SQL.So

Either use mysql Prepare Execute statement Or use PHP way like:

1)This would be your 1st Query as above.You will get All column names Excluding primary.OK?

2)Now fetch the resulted array in PHP.and build dynamic query for Comma separated Column names you just got.

3)Now fire 2nd query to fetch ROWS .Here you will include SELECT (Column1,Column2) from TBL.

And after getting column names , you can fire query to return Result based on Returned columns

Pratik Joshi
  • 11,485
  • 7
  • 41
  • 73
  • thanks, but this error returned : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW INDEXES FROM TABLE WHERE Key_name <> "PRIMARY") from tbl1' at line 1 – Behzad Azizan Oct 27 '14 at 06:09
  • @Behzad , updated answer, replace table,DB names by Your Own tables wp_countries , wpresstest – Pratik Joshi Oct 27 '14 at 06:13
  • thanks a lot but again error :( ... I use this SELECT (SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE (`TABLE_SCHEMA` = 'test') AND (`TABLE_NAME` = 'tbl1') AND (`COLUMN_KEY` <> 'PRI') ) FROM tbl1 – Behzad Azizan Oct 27 '14 at 06:20
  • and this error returned : #2014 - Commands out of sync; you can't run this command now – Behzad Azizan Oct 27 '14 at 06:21
  • first use Simple select to fetch all COLUMNS without primary. then go for nested SELECT , wat did u get in Simple SELECT? – Pratik Joshi Oct 27 '14 at 06:23
  • @Behzad , can you plz work around it ? m in office having imp deployment.I cant answer half part of your question immediately. – Pratik Joshi Oct 27 '14 at 06:45
  • I can't understand your text. could you write better? – Behzad Azizan Oct 27 '14 at 06:55
  • Thank you. Query optimization is very important for me – Behzad Azizan Oct 27 '14 at 07:18