[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
>
>