CODEWITHSUNDEEP
sqlms-accessvb6vba
erasmo carlos
erasmo carlos

Reputation: 682

Convert VBA Update query to SQL syntax

I am working on an access application that has many inline sql queries, the syntax is very similar as the syntax on regular queries executed directly in ssms, but there are some differences. Now I ran into an update query that I don't understand. It uses an inner join right after the update statement, but ssms does not like the way that this query is constructed.

I am asking for help to understand how the query should be correctly converted, because I do not want to update the wrong table at all.

This is the query without conversion:

UPDATE Product
INNER JOIN 
(
    (   Arrangement 
        INNER JOIN Publishers 
            ON Arrangement.PubKey = Publishers.BookPubKey
    ) 
    INNER JOIN Arrangement_Notation_File 
        ON Arrangement.ArrangementID = Arrangement_Notation_File.ArrangementID
) 
ON Product.ProductID = Arrangement_Notation_File.ProductID 

SET 
    Product.Title = [arrangement].[Title], 
    Product.ProductType = ProductType, 
    Product.ForSale = -1, 
    Product.IsDigital = -1, 
    Product.ListPrice = [Arrangement].[Price], 
    Product.PublicationDate = iif(isnull(FirstPublicationDate), iif(isnull(PublicationDate), '" & Now() & "', PublicationDate), FirstPublicationDate), 
    Product.SongID = Arrangement.SongID  

WHERE (((Arrangement_Notation_File.ProductID)='" & ID & "'));

Upvotes: 1

Views: 66

Answers (1)

Rahul
Rahul

Reputation: 77866

What if you try like below. Notice that the SET and JOIN have been interchanged and made the JOIN proper.

UPDATE P
SET 
    Product.Title = [arrangement].[Title], 
    Product.ProductType = ProductType, 
    Product.ForSale = -1, 
    Product.IsDigital = -1, 
    Product.ListPrice = [Arrangement].[Price], 
    Product.PublicationDate = iif(isnull(FirstPublicationDate), iif(isnull(PublicationDate), '" & Now() & "', PublicationDate), FirstPublicationDate), 
    Product.SongID = Arrangement.SongID 
FROM Product P
INNER JOIN Arrangement_Notation_File ANF ON P.ProductID = ANF.ProductID 
INNER JOIN Arrangement A ON A.ArrangementID = ANF.ArrangementID
INNER JOIN Publishers PU ON A.PubKey = PU.BookPubKey
WHERE ANF.ProductID ='" & ID & "';

Upvotes: 2

Related Questions