Ad:

Database Design Discussion Thread
Marked Solved
Views: 4413

Aug 29th, 2007

SQL Query Analyzer Problem

Expand Post »

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...

(Toggle Plain Text)

 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!

Similar Threads

sql query problem with MS Access and C# in C#
how to delete duplicate record in a table by using SQL query in MS SQL
SQL query problem with WHERE clause in ASP
Accessing ADSI Query from ASP page in ASP
sql query updating problem in Visual Basic 4 / 5 / 6

SerinaKelley

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Aug 2007

Permalink

Aug 29th, 2007

Re: SQL Query Analyzer Problem

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.

ndarechuk

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

1 posts
since Aug 2007

Permalink

Aug 30th, 2007

Re: SQL Query Analyzer Problem

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.

SerinaKelley

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Aug 2007

Permalink

Aug 30th, 2007

Re: SQL Query Analyzer Problem

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

(Toggle Plain Text)

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.

(Toggle Plain Text)

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

Last edited by hollystyles; Aug 30th, 2007 at 9:18 am.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

Aug 30th, 2007

Re: SQL Query Analyzer Problem

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?

SerinaKelley

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Aug 2007

Permalink

Aug 30th, 2007

Re: SQL Query Analyzer Problem

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.

Last edited by hollystyles; Aug 30th, 2007 at 9:41 am.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Database Design Forum Timeline: DB design for friends network

Next Thread in Database Design Forum Timeline: Design help plz

DaniWeb Message

Cancel Changes

User Name
Password