Darryl Collins
darryl at whittleconsulting.com.au
Tue Jan 7 16:20:30 CST 2014
Thanks Guys, Appreciate the feedback. I am sure I will have some other issues I am going to need help with, although I generally try to figure them out first rather than just palming off the work to the list. Mark: thanks for your suggestion too - it is good to see alternative methods to these things. Cheers Darryl. -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark Breen Sent: Tuesday, 7 January 2014 8:30 PM To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Stored Procedure Syntax using "Update" Hello Darryl, yes, that is the way we would write it over here on SQL - we are a small but beautiful list :) Sometimes, for various reasons I may struggle to use this approach and I use the following as a less elegant but still functional alternative '----------------------------------------------------------- ------------------------------------------------------- UPDATE dbo.Key_tblPortions SET dbo.Key_tblPortions.EveID = dbo.Temp_tblPortionSteps.EveID Where dbo.Key_tblPortions.Id In ( Select Id from vw_Complex_Set_Of_Criteria ) '----------------------------------------------------------- ------------------------------------------------------- The added benefit of this approach is you can see the Ids before you run the update / delete. In summary, your first approach is the best one. This is just FYI. HTH Mark On 7 January 2014 05:33, Darryl Collins <darryl at whittleconsulting.com.au>wrote: > > Aaaah.... That is typical. Sent this email. Figured it out about 30 > seconds later. Duh! :) > > > > '--------------------------------------------------------------------- > --------------------------------------------- > UPDATE dbo.Key_tblPortions > SET dbo.Key_tblPortions.EveID = dbo.Temp_tblPortionSteps.EveID > > FROM dbo.Key_tblPortions > > INNER JOIN dbo.Temp_tblPortionSteps > ON dbo.Key_tblPortions.EveID = dbo.Temp_tblPortionSteps.EveID > > '--------------------------------------------------------------------- > --------------------------------------------- > > Cheers > Darryl > > > > > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com [mailto: > dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Darryl > Collins > Sent: Tuesday, 7 January 2014 4:18 PM > To: dba-sqlserver at databaseadvisors.com > Subject: [dba-SQLServer] Stored Procedure Syntax using "Update" > > Hi There, > > I am hoping someone can help me out here, although it seems rather > quiet on this list, but hey, I will try here first. > > Using SQL Server 2008 R2 express. > > Been migrating an existing MS Access Solution to SQL Server and so far > things have been going fairly well. However I have bumped into an > issue when updating a bit of MS Access code to a SQL Server Stored > Proc. Indeed, maybe I don't even need to use a Stored Proc as I have > some code that passes the SQL String directly to the server via VBA > and can run it that way. > > For some reason using an SP seemed the better solution, but I am open > to Ideas and opinions here. > > The Current VBA code is > > UPDATE > Key_tblPortions > INNER JOIN Temp_tblPortionSteps > ON Key_tblPortions.PortionID = Temp_tblPortionSteps.PortionID SET > Key_tblPortions.EveID = [Temp_tblPortionSteps].[EveID] > > > The SP code on the Server is pretty much identical (which usually work > fine, albeit with some minor tweaks if necessary) > > UPDATE > dbo.Key_tblPortions > INNER JOIN > dbo.Temp_tblPortionSteps > ON > dbo.Key_tblPortions.PortionID = dbo.Temp_tblPortionSteps.PortionID > SET dbo.Key_tblPortions.EveID = [dbo.Temp_tblPortionSteps].[EveID] > > However in this instance SQL Server is returning the error > > "Incorrect syntax near the keyword 'INNER'." > > Which is both perfectly clear but confusing for me at the same time. > > The set up is I have two tables linked on and ID field (PortionID). > > The EveID field in Table2 should be written in to the matching EveID > field in Table1 where their portionID's match. > > Simple enough, but not sure how to do this in the SP on SQL Server. > > Any suggestions? > > Cheers > Darryl. > _______________________________________________ > 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