July 27, 2016 at 3:16 pm
So I ran into a funny one today, I saw a delete operation on a view with a join in it, where the view returned columns from both tables. So how does SQL Server determine what table to delete from?
When I tried a straight up delete on it, I got:
Msg 4405, Level 16, State 1, Line 6
View or function 'header_detail' is not updatable because the modification affects multiple base tables.
But when the view was the target in a merge statement, all rules went out of the window. My problem is that I just really made a trivial example of some production code to understand how this works, and I'm betting if this is really a bogus operation, I'm going to be hard put to convince folks that using this construct is a bad idea. Anybody have any ideas on this? Feature, bug?
The worst part is that while I was hoping the detail would get deleted, in my small example the header actually got deleted, I'm guessing because the luck of the plan that got selected.
create table detail
(
id int not null,
header_id int not null,
detail_data varchar(50) null,
constraint pk_detail primary key clustered
(
id,
header_id
)
)
go
create table header
(
idh int not null,
header_data varchar(10) null,
constraint pk_header primary key clustered
(
idh
)
)
go
create view header_detail (id, detail_data, header_id, header_data)
as
select id, detail_data, header_id, header_data
from header join detail
on idh = header_id
go
truncate table header
insert into header
select 1, 'header 1'
insert into header
select 2, 'header 2'
insert into header
select 3, 'header 3'
truncate table detail
insert into detail
select 1, 1, 'header 1, detail 1'
insert into detail
select 2, 1, 'header 1, detail 2'
insert into detail
select 3, 1, 'header 1, detail 3'
insert into detail
select 4, 2, 'header 2, detail 4'
insert into detail
select 5, 2, 'header 2, detail 5'
delete from header_detail
where detail_data = 'header 2, detail 5' and id = 5
-- gives:
-- Msg 4405, Level 16, State 1, Line 6
-- View or function 'header_detail' is not updatable because the modification affects multiple base tables.
merge header_detail hd1
using
(
select 5 srckey, 'header 2, detail 5' srcdata
) t1
on hd1.detail_data = t1.srcdata
and hd1.id = t1.srckey
when matched and id = 5 then delete
;
select * from header
select * from detail
July 28, 2016 at 6:42 am
That is weird.
I tried a few things like having the ON condition include both tables, and it still worked.
Always updated from the header table, not the detail table, so I reversed the order of the tables in the View definition. It started deleting from detail instead of header.
It seems to violate rules, but it does appear to be consistent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2016 at 7:26 am
GSquared (7/28/2016)
That is weird.I tried a few things like having the ON condition include both tables, and it still worked.
Always updated from the header table, not the detail table, so I reversed the order of the tables in the View definition. It started deleting from detail instead of header.
It seems to violate rules, but it does appear to be consistent.
Interesting that the join order affected it, I would assume its equivalent if its an inner join. I duplicated the join order that occurs in the actual production code, but there it deletes details, and I'm wondering if its just based on what the plan puts together.
As for the production code, I'm probably going to let pragmatism override my offended sensibilities and leave it alone for a bit, but its not like I've never let work issues bug me to a disproportionate degree before, and that's a bug in MY programming LOL
July 28, 2016 at 8:12 am
This is interesting. I'm not sure if that's a bug or a feature. I would be inclined to call it a bug.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply