Search code examples
sqlsql-serversybase

How to remove/rename a duplicate column in SQL (not duplicate rows)

When attempting to do an OPENQUERY from Sybase to Microsoft SQL I run into an error:

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "PatientID" is a duplicate.

The query I have built joins 2 tables based on similar admissionID and patientID.

For Instance:

PatID   AdmID   Loc  PatID  AdmID   Doctor 
1         5      NC    1      5      Smith 
2         7      SC    2      7      Johnson

The real query of course has a ton more information than just this.

Is there a good way to rename or remove one of the AdmID and PatID columns?

I attempted:

SELECT * INTO #tempTable
ALTER #tempTable
DROP COLUMN PatID

This does not work since PatID is ambiguous.

I also attempted:

SELECT firstTable.PatID as 'pID', * FROM...

This does not work either.

Solution

  • You'll have to alias one of the two duplicate columns and explicitly indicate specific columns in the select at least for one of the tables (the one you've used the alias on it's column):

     SELECT firstTable.PatID as 'pID', firstTable.column2, secondTable.* FROM...

    Notice, I've still used a wildcard on the secondTable.

    However....

    I'd avoid using * wildcards altogether and try to always indicate exactly the columns you need.