CTE error 4405

[mkrausnick]

I'm getting

Msg 4405, Level 16, State 1, Line 1
View or function 'overlap' is not updatable because the modification affects multiple base tables.

However, I'm only referencing one table.

The purpose of the CTE is to identify and consolidate overlapping insurance enrollment periods. It is run from a stored procedure stored in a different database. 'ACE' is the code for the insurance program.

Here's the CTE:

;with overlap as (
select Enrollment_ID as Ace1Enrollment_ID,pid as Ace1Pid,Ins_Eff_Dt as Ace1EffDate,Ins_End_Dt as Ace1EndDate, 
	ace.AceEnrollment_ID, ace.acepid,ace.aceEffDate,ace.aceEndDate,
	case when Ace1.Ins_Eff_Dt < ace.aceEffDate then Ace1.Ins_Eff_Dt else ace.aceEffDate end as MinEffDate,
	case when Ace1.Ins_End_Dt > ace.aceEndDate then Ace1.Ins_End_Dt else ace.aceEndDate end as MaxEndDate
	from [Oneeapp].[dbo].oea_program_enrollment Ace1 
inner join (
	select Enrollment_ID as aceEnrollment_ID,pid as acePid,
			Ins_Eff_Dt as aceEffDate,Ins_End_Dt as aceEndDate , 
			CONVERT(datetime,'') as ModStartDate
		from [Oneeapp].[dbo].oea_program_enrollment	where Prog_Id = 'ACE') ace
	on Ace1.pid = acepid and Ace1.Ins_Eff_Dt between aceEffDate and aceEndDate and Ace1.Ins_End_Dt > aceEndDate
	where  Ace1.Prog_Id = 'ACE' and Ace1.Enrollment_ID <> Ace.aceEnrollment_ID )
update overlap set Ace1EffDate=MinEffDate, Ace1EndDate=MaxEndDate,aceEffDate=MinEffDate,aceEndDate=MaxEndDate

Only one table is referenced, right? Any ideas as to why this doesn't work?

Thanks for your help.

Mike Krausnick
Dublin, California

 

[SQLBill]

I'm not familiar with the 'overlap' thing, so I could be wrong about this. But I believe that is two tables. One just happens to be a derived table, but it is still a table.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

Two tables are referenced in the query.

PluralSight Learning Library

 

[fredericofonseca]

and you are trying to update a CTE (overlap), and update fields from both the CTE and the underlying table oea_program_enrollment.

It kind of doesn't make sense to me.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mkrausnick]

Hmmm. I don't understand. The entire CTE is based on only one table: oea_program_enrollment. It is joined to itself. Does CTE take the two parts of the join to be separate tables even though they are based on the same underlying table? To make things even more confusing, the following CTE, on which the one I posted is based, does not get the error. The purpose of this is similar: to detect and resolve enrollment period conflicts among multiple insurance plans.

- CMSP period overlaps end of ACE period; set CMSP start date to follow ACE end date
;with overlap as (
select Enrollment_ID as cmspEnrollment_ID,pid as CmspPid
	,Ins_Eff_Dt as CmspEffDate,Ins_End_Dt as CmspEndDate, 
	ace.AceEnrollment_ID, ace.acepid,ace.aceEffDate,ace.aceEndDate
	from [Oneeapp].[dbo].Oea_program_enrollment cmsp 
inner join (
	select Enrollment_ID as aceEnrollment_ID,pid as acePid
			,Ins_Eff_Dt as aceEffDate,Ins_End_Dt as aceEndDate 
			, CONVERT(datetime,'') as ModStartDate
		from [Oneeapp].[dbo].Oea_program_enrollment	where Prog_Id = 'ACE') ace
	on cmsp.pid = acepid 
		and Cmsp.Ins_Eff_Dt between aceEffDate and aceEndDate 
		and Cmsp.Ins_End_Dt > aceEndDate
	where  cmsp.Prog_Id = 'CMSP' )
update overlap set cmspEffDate=dateadd(d,1,aceEndDate)

I note that in first CTE, I'm updating both "halves" of the self-join, whereas in the second CTE I'm updating only one half of the self-join. Could that be the issue?


Mike Krausnick
Dublin, California

 

[fredericofonseca]

on this last example you are only updating ONE table, not two as on your original post, so yes I would say that is the issue

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886