SPROC ERROR - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SPROC ERROR

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-21 : 11:58:32

Hi
i get an error for the following sproc. Any help in rectifying this

Update B
set B.ASC_CHANGES = 2 from ASCG as A
inner join (select * from ASCG where asc_Eff_Start = '04/01/2008') as B
on A.ASC_HCPCS = B.ASC_HCPCS
where A.asc_Eff_Start = '01/01/2008'
and a.asc_base <> b.ASC_BASE
and B.ASC_CHANGES is null
and b.asc_link is null

create procedure sp_ASCratechange
@date1 varchar(10),
@date2 varchar(10)
as
Update B
set B.ASC_CHANGES = 2 from ASCG as A
inner join (select * from ASCG where asc_Eff_Start = @date2) as B
on A.ASC_HCPCS = B.ASC_HCPCS
where A.asc_Eff_Start = @date1
and a.asc_base <> b.ASC_BASE
and B.ASC_CHANGES is null
and b.asc_link is null

execute sp_ASCratechange '01/01/2008', '04/01/2008'

Msg 4421, Level 16, State 1, Procedure sp_ASCratechange, Line 5
Derived table 'B' is not updatable because a column of the derived table is derived or constant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:53:10

you've a derived or constant column in ASCG so use only required columns in select rather than *
i.e


Update B
set B.ASC_CHANGES = 2 from ASCG as A
inner join (select ASC_HCPCS,ASC_BASE,ASC_CHANGES,asc_link from ASCG where asc_Eff_Start = '04/01/2008') as B
on A.ASC_HCPCS = B.ASC_HCPCS
where A.asc_Eff_Start = '01/01/2008'
and a.asc_base <> b.ASC_BASE
and B.ASC_CHANGES is null
and b.asc_link is null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources