SQL Query Analyzer Problem

[SerinaKelley]

The program I use is housed in a SQL Database. What I am trying to do is term Members based on meeting 2 guidelines (PCP and Insurance). WHat I want to do is term a group of members that have the same PCP and Insurance plan. This data is broken up into 3 different databases - Pcphistory, Eligibilityhistory, and Benefitplans. I have been struggling, because I have never had to do this before. This is what I started...

update eligibilityhistory set terminationdate='09/30/2007'
from eligibilityhistory inner join benefitplans on eligibilityhistory.benefitplanid =benefitplans.benefitplanid 
where benefitplans.healthplanid='BCN' 
from eligibilityhistory inner join pcphistory on eligibilityhistory.memberid = pcphistory.memberid
where pcphistory.providerid='1532' 
and terminationdate is null

When I run this Query in SA, I get the following error: Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.

This is running on SQL 2000... I am truly pulling my hair out, as there isn't anyone who uses the software that we do and the programmer bailed on us!

[ndarechuk]

Try removing 'from eligibilityhistory' after 'BCN'. This is your main table so it is not necessary to restate what table the value is to be retreived from.

Depending on what app you are using to run your query, line numbers usually reflect the line after or before.

[SerinaKelley]

I did try and remove the 'from eligibilityhistory' and that didn't work. Then I get this error message: Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.

I am using SQL Query Analyzer for running queries. I examine my database structure with Enterprise Manager.

[hollystyles]

This is now syntactically correct, whether it does what you require I can only guess without DDL and expected results from you.

update 
    eligibilityhistory 
set 
    terminationdate='09/30/2007'
from 
    eligibilityhistory 
    inner join benefitplans 
        on eligibilityhistory.benefitplanid=benefitplans.benefitplanid 
    inner join pcphistory
        on eligibilityhistory.memberid=pcphistory.memberid
where 
    benefitplans.healthplanid='BCN' 
    and pcphistory.providerid='1532'
    and terminationdate is null

I recommend you run this first to see what is going to be updated before you actually run the update above.

Select 
    *
from 
    eligibilityhistory 
    inner join benefitplans 
        on eligibilityhistory.benefitplanid=benefitplans.benefitplanid 
    inner join pcphistory
        on eligibilityhistory.memberid=pcphistory.memberid
where 
    benefitplans.healthplanid='BCN' 
    and pcphistory.providerid='1532'
    and terminationdate is null

[SerinaKelley]

The syntax looks great. I am now down to getting one error:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'terminationdate'.

I am not sure why it comes up Ambiguous, as the other queries I run (without the extra inner join) all work correctly. Any last ideas?

[hollystyles]

This means terminationdate is a column name in more than one of the three tables in your SQL Query. You need to be Specific (not ambiguous) and prefix it with the table name and a period. Hint double click on the error message in the lower pane of Query Analyzer it will take you to the line that has the error.

You will need to do this in the 'where' clause. So if it's the terminationdate column in the pcphistory table that must be null put pcphistory.terminationdate is null in the where clause.