erasmo carlos erasmo carlos - 5 months ago 21
SQL Question

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 & "'));

Answer

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 & "';