Help With An Update Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help With An Update Script

Author

Topic

bosstone75
Starting Member

11 Posts

Posted - 2010-01-26 : 14:32:20

Hello,

I'm rather new to writing SQL scripts and if you could help me out that would be GREAT!

What I am wanting to do is update information in a table, but only if it meets criteria in TWO tables that are in the database.

The script I am currently trying is...

update DPRSPlayschool.dbo.plans

set previousstatus = status,
status = 'N',
statusreason = 'This plan was found in draft mode on 1/26/2010 and was more than a year old.',
updatedate = '1/26/2010',
updaterID = 'BillsDraftCleanUpScript'

WHERE cases.CaseID = plans.CaseID AND ((cases.CaseStatus<>'X') AND (plans.PlanStatus='D') AND (plans.PlanDate<{ts '2009-09-08 00:00:00'}))

When I use that script, I get the following error message...

Msg 107, Level 16, State 3, Line 1
The column prefix 'cases' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Line 1
The column prefix 'cases' does not match with a table name or alias name used in the query.

I have a database named DPRSPlayschool.
In that database is a table called cases that determines if the student in question is even active in our school district anymore.
The table that I would like to change is called plans

So I wanted to check to see if the student was active in the cases table (not X) AND limit the changes to the plans table to plans that are if Draft mode (D) and that were created before 9/8/2009.

It looks like the only thing it's getting confused by in my current script is the fact that I'm trying to reference the cases table.

Any ideas? Do you need more info?

Thanks!

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 14:37:06

Something like this I expect:


update U
set previousstatus = status,
    status = 'N',
    statusreason = 'This plan was found in draft mode on 1/26/2010 and was more than a year old.',
    updatedate = '1/26/2010',
    updaterID = 'BillsDraftCleanUpScript'
FROM DPRSPlayschool.dbo.plans AS U
    JOIN cases AS C
        ON C.CaseID = U.CaseID
       AND C.CaseStatus<>'X'
WHERE      U.PlanStatus='D'
       AND U.PlanDate<{ts '2009-09-08 00:00:00'}

bosstone75
Starting Member

11 Posts

Posted - 2010-01-26 : 15:37:05

Excellent! Thank you very much. Worked like a charm.

Hopefully I'll be able to apply this Join to other ideas I have!

Take care,
Bill

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 15:56:55

And don't invoke ODBC calls in T-SQL queries!

WHERE U.PlanStatus='D'
AND U.PlanDate< '2009-09-08 00:00:00'

will be enough...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources