[dba-SQLServer] View not updatable because

Try something like this:

Delete from MyTable1 where PK in (select PK from MyTable2)  

Not as quick as you will like.  But it will do what you want. 

HTH, 

Liz 

 

This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia
Sent: Wednesday, October 28, 2009 2:54 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] View not updatable because

I think you want to write up your statement as a delete statement, not  
adelete from view but a delete from a table does that make sense?
The result is that you'd use a sproc (stored procedure) to help delte  
records and not a standard statement.

Sent from my mobile

On Oct 28, 2009, at 9:38 AM, jwcolby <jwcolby at colbyconsulting.com>  
wrote:

> What I have found on GOOGLE is that the FROM clause can only  
> reference a single table.  But if I am
> going to select records by a join, then by definition the where  
> clause will reference more than one
> table.
>
> HELP!!!
>
> TIA.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> jwcolby wrote:
>> I have never figured out how to do the following...
>>
>> I have a table which contains records that I need to delete.  I  
>> create a view that joins that table
>> to another table on the PKID.  The second table has a set of  
>> records which I need to delete out of
>> the first table, then append into the first table.  IOW the  
>> matching records in the first table have
>> have been updated and I need to delete them, then append in the  
>> updated records.
>>
>> I get an error message:
>>
>> "view or function MyFunction is not updatable because the  
>> modification affects multiple base tables."
>>
>> This is a simple view witj two tables, joined on the PKID, all  
>> fields selected in the table that
>> needs records updated (deleted), NO fields selected in the second  
>> table.
>>
>> What am I doing wrong?
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com