Jim Lawrence
accessd at shaw.ca
Tue Jan 7 01:20:24 CST 2014
Hi Darryl: I was just about say that. ;-) Jim ----- Original Message ----- From: "Darryl Collins" <darryl at whittleconsulting.com.au> To: "Discussion concerning MS SQL Server" <dba-sqlserver at databaseadvisors.com> Sent: Monday, January 6, 2014 9:33:48 PM Subject: Re: [dba-SQLServer] Stored Procedure Syntax using "Update" 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