jwcolby
jwcolby at colbyconsulting.com
Wed Oct 28 15:25:34 CDT 2009
> Not as quick as you will like. But it will do what you want. Actually that was BLAZING fast! Seven seconds to delete a million rows. John W. Colby www.ColbyConsulting.com Elizabeth.J.Doering at wellsfargo.com wrote: > 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 > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >