[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