SQL syntax for update query

Question

I have an update query in MS Access like this:

UPDATE ([tblDocument-VP] 
INNER JOIN [tbltransmittals-VP] ON [tblDocument-VP].OwnerDocumentNo = [tbltransmittals-VP].OwnerDocumentNo) 
INNER JOIN tblVendorName ON [tblDocument-VP].[Vendor Name] = tblVendorName.[VENDOR NAME] 
SET [tbltransmittals-VP].HyperDoc = [tblDocument-VP]![OwnerDocumentNo] + '-' + [tbltransmittals-VP].[REV] + '#' + [root]+[tblVendorName]![VendorDesc] + '\' + [tblDocument-VP]![Tag No] + '\' + [tblDocument-VP]![OwnerDocumentNo] + '-' + [REV] + '.pdf' + '#';

I would like to create a view of this in SQL Server, but when I try to do that, I get an error

Incorrect syntax near '('"

I do not know what is problem, also i like to know can I use a view as a update query?

what do you mean with that you want to create a view of this in sql server ? An update cannot be in a view. Do you want to do the same update in sql server or what do you mean ? — GuidoG, Oct 11 '17 at 06:51

score 0 · Accepted Answer · answered Oct 11 '17 at 06:54

0

You can't run a UPDATE inside a View.

A view can only be used to read/show data.
What you need is a Stored Procedure.

Also you have multiple syntax errors in your UPDATE-query.
Here's a good explanation for updates with INNER JOIN's

answered Oct 11 '17 at 06:54

MatSnow

7,357
3
19
31

score 0 · Answer 2 · answered Oct 11 '17 at 08:16

Expanding on to GuidoG's comment, the SP should be like

CREATE PROCEDURE uspYourProcedureName  
AS
    UPDATE t2
    SET t2.HyperDoc = 
                t1.[OwnerDocumentNo] + '-' + 
                t2.[REV] + '#' + [root]+
                t3.[VendorDesc] + '\\' + t1.[Tag No] + '\\' + t1.[OwnerDocumentNo] + '-' + [REV] + '.pdf' + '#'
    FROM [tblDocument-VP]  t1
    INNER JOIN [tbltransmittals-VP] t2
            ON t1.OwnerDocumentNo = t2.OwnerDocumentNo 
    INNER JOIN tblVendorName t3
            ON t1.[Vendor Name] = t3.[VENDOR NAME] 
GO

SQL syntax for update query

2 Answers2