Updating Base Table from a View of that Table joined to other Views

Question

Updating Base Table from a View of that Table joined to other Views

r.pe

SSC Eights!

Points: 835
More actions
June 30, 2009 at 8:54 am

#211706

Hi all. This might have been covered before but I was wondering if you can update the base table; in this case the Microsoft CRM Extension Base table from a view created from that table in conjunction with joins to other views ( this may be immaterial )
In this case the PA_renewalbillingExtensionBase is the base table and the view is FilteredPA_renewalbilling :
Here is the sample:
UPDATE PA_renewalbillingExtensionBase SET PA_terminationdate = '2009-06-29'
,PA_terminationreason = 18
,PA_benefitstatus = 3
FROM FilteredContact a inner join FilteredPA_renewalbilling b on a.contactid = b.pa_customercontactid
left outer join FilteredInvoice i on b.pa_invoiceid = i.invoiceid
WHERE
a.pa_member = 1 AND
b.pa_benefitidname LIKE 'ASA%' AND
b.pa_benefitidname <> 'ASA Newsletter' AND
b.pa_benefitidname <> 'ASA Test' AND
b.pa_benefitstatusname = 'New' AND
b.pa_benefitstatus <> 3 AND
a.statuscode = 1 AND
b.statecode <> 1
and b.statuscode <> 2 and
((b.pa_currentjoindate between '2009-01-01' and '2009-03-31') or
(b.pa_originaljoindate between '2009-01-01' and '2009-03-31'))
and pa_balancedue > 0
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I don't think you can do it the way you have it, the DB engine has no way of knowing what row in the base table to update, you could add the base table and join the view on that table to the table based on the primary keys and THEN I think it would work. I wouldn't be surprised if you were getting an error, and if not I'm not sure what kind of update you would be doing..

r.pe SSC Eights! Points: 835 More actions · Answer 2

I agree ... I guess I needed another set of eyes. We are going to be using all the base tables perhaps using a cursor in a stored proc. The subset of records to be changed is very small anyway.

Thanks.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

r.pe (6/30/2009)
I agree ... I guess I needed another set of eyes. We are going to be using all the base tables perhaps using a cursor in a stored proc. The subset of records to be changed is very small anyway.

:sick:

You don't need a cursor to handle this. Just add your base table into the JOIN and use the alias to update:

UPDATE c SET PA_terminationdate = '2009-06-29'

,PA_terminationreason = 18

,PA_benefitstatus = 3

FROM FilteredContact a

inner join FilteredPA_renewalbilling b on a.contactid = b.pa_customercontactid

INNER JOIN PA_renewalbillingExtensionBase c on b.YourId = c.YourId

left outer join FilteredInvoice i on b.pa_invoiceid = i.invoiceid

WHERE

a.pa_member = 1 AND

b.pa_benefitidname LIKE 'ASA%' AND

b.pa_benefitidname 'ASA Newsletter' AND

b.pa_benefitidname 'ASA Test' AND

b.pa_benefitstatusname = 'New' AND

b.pa_benefitstatus 3 AND

a.statuscode = 1 AND

b.statecode 1

and b.statuscode 2 and

((b.pa_currentjoindate between '2009-01-01' and '2009-03-31') or

(b.pa_originaljoindate between '2009-01-01' and '2009-03-31'))

and pa_balancedue > 0

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I couldn't agree more, there is no reason to incur the overhead of a cursor, just adding the base table with a join on the keys should work great.

CEWII