1

I am trying to drop a number of columns after joining three tables. I came across a very useful SO post: SQL exclude a column using SELECT * [except columnA] FROM tableA?

However I can't get the solution I found therein (the top answer) to work for my case; joining multiple tables, and want to drop the key that is used for the second and third tables, as well as some other columns.

Here is a simplified case whereby I'm just attempting to drop the key of the second table, which comes out as mykey_2:

SELECT * INTO Temptable
FROM
table_1 INNER JOIN table_2 ON 
table_1.mykey=table_2.mykey INNER JOIN 
table_3 ON table_1.mykey= table_3.mykey
WHERE table_1.A_FIELD = 'Some Selection'
ALTER TABLE Temptable
DROP COLUMN table_2.mykey
GO
SELECT * FROM Temptable
DROP TABLE Temptable

My console is giving me the error "ORA-00933: SQL command not properly ended".

How can I achieve this dropping of specific columns from the final selection?

For information I'm querying an Oracle database using Toad.

Community
  • 1
  • 1
acolls_badger
  • 423
  • 1
  • 9
  • 29
  • 7
    Why don't you just select the columns you need...? – Siyual Sep 25 '14 at 15:48
  • 2
    `GO` isn't valid for Oracle. The question (and answer) you linked to are for SQL Server. And temporary tables work very differently in Oracle too. Really, just don't use `SELECT *`, list out the columns actually need from each table. – Alex Poole Sep 25 '14 at 15:58
  • There's is a very large number of columns and I only want to drop a small proportion of them, selecting them all would be arduous. If that's the only way though then that's the only way. Many thanks. – acolls_badger Sep 25 '14 at 16:05
  • 1
    You might find a shortcut to generate the select-list in the answers to [this question](http://stackoverflow.com/q/9133120/266304). – Alex Poole Sep 25 '14 at 16:08

0 Answers0