-1

I'm at a loss on what to do. I'm trying to fix an old broken query that was messing something up. If I use the database2 script, it populates what I want. However, when used with the Update, it gives the syntax error.

USE Database
UPDATE [Database].[dbo].[tblParts]
SET [Database].[dbo].[tblParts].[Cost2] = StdCost
FROM (SELECT [Database2].[dbo].[itemnoninventory].[FullName], [Database2].[dbo].[itemnoninventory].[CustomField2] AS StdCost
FROM [Database2].[dbo].[itemnoninventory]
INNER JOIN
[Database2].[dbo].[salesorpurchasedetail] ON [Database2].[dbo].[itemnoninventory].[ListID] = [Database2].[dbo].[salesorpurchasedetail].[IDKEY]
INNER JOIN [Database].[dbo].[tblParts] ON [Database2].[dbo].[itemnoninventory].[FullName] like [Database].[dbo].[tblParts].[PartNo]
WHERE ([Database2].[dbo].[salesorpurchasedetail].[AccountRef_FullName] = 'Word, Another Word'))

error:

Msg 102, Level 15, State 1, Line 9 Incorrect syntax near 'Word, Another Word'.

1

You have created a derived table, but didn't give it a name. So, after you final closing parentheses, you need to alias this derived table.

Also, I don't know why you are doing the following, but didn't change them. I just found it odd.

  • Returning the FullName column since you aren't using it
  • Self joining to Database2
  • Using Like without %

Here's the updated query with a comment at the bottom

USE Database
UPDATE [Database].[dbo].[tblParts]
SET [Database].[dbo].[tblParts].[Cost2] = StdCost
FROM (  SELECT 
            [Database2].[dbo].[itemnoninventory].[FullName], 
            [Database2].[dbo].[itemnoninventory].[CustomField2] AS StdCost
        FROM 
            [Database2].[dbo].[itemnoninventory]
        INNER JOIN
            [Database2].[dbo].[salesorpurchasedetail] ON [Database2].[dbo].[itemnoninventory].[ListID] = [Database2].[dbo].[salesorpurchasedetail].[IDKEY]
        INNER JOIN 
            [Database].[dbo].[tblParts] ON [Database2].[dbo].[itemnoninventory].[FullName] like [Database].[dbo].[tblParts].[PartNo]
        WHERE 
        ([Database2].[dbo].[salesorpurchasedetail].[AccountRef_FullName] = 'Word, Another Word')
    ) as SomeName   --name it here

I think you could simplify it as:

UPDATE D
SET D.[Cost2] = D2.StdCost
FROM 
    [Database] D
    INNER JOIN
    [Database2].[dbo].[itemnoninventory] D2 ON
     D.[PartNo] like '%' + D2.[FullName] + '%'
WHERE
    D2.[AccountRef_FullName] = 'Word, Another Word'
  • odd comment: I'm just trying to make it work right again without changing much. That stuff was there since creation. – SQLsyntaxerror Dec 3 '18 at 19:19
  • To make it clear: I created a new column to test this so that the normal column is not changed. Well, the query runs, but changes all the values to the same result.If I run the select, it gives them all as expected. – SQLsyntaxerror Dec 3 '18 at 19:23
  • added this and seems to give what I expect: ) AS SomeName WHERE SomeName.[FullName] = [Database].[dbo].[tblParts].[PartNo] – SQLsyntaxerror Dec 3 '18 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.