I need help understanding what this sql query is doing

Question

I am trying to help fix this database but I do not understand what this SQL query is doing. It should be updating the field "Supplier Part Number" in the table "tblSpend" but it isnt doing that.

SQL:

UPDATE tblSpend
 SET [Supplier Part #] = ISNULL(ts.[Supplier Part Number], '')
 FROM (SELECT DISTINCT tblSpend.[Hussmann Item #], tmn.[ITEM NO], ISNULL(tmn.[Supplier Part Number], N'') AS [Supplier Part Number],
 tmn.Year, tmn.[COMPANY NAME], tblSpend.[Standard Supplier Name]
 FROM (SELECT CAST(REPLACE(REPLACE([ITEM NO], ' ', ''), '-', '') AS char(60)) AS [ITEM NO Clean], [ITEM NO], [COMPANY NAME], Year, [Supplier Part Number]
 FROM tblMACOONew) AS tmn RIGHT OUTER JOIN tblSpend ON tmn.[ITEM NO Clean] = CAST(REPLACE(REPLACE(tblSpend.[Hussmann Item #], ' ', ''), '-', '') AS char(60))
 AND tmn.[COMPANY NAME] = tblSpend.[Standard Supplier Name] WHERE (tmn.Year = '2024')) AS ts RIGHT OUTER JOIN
 tblSpend ON ts.[Hussmann Item #] = tblSpend.[Hussmann Item #] AND ts.[COMPANY NAME] = tblSpend.[Standard Supplier Name];

Answer 1

a formatted query would help:

UPDATE tblSpend
    SET [Supplier Part #] = ISNULL(ts.[Supplier Part Number], '')
FROM
(
	SELECT DISTINCT 
		tblSpend.[Hussmann Item #], 
		tmn.[ITEM NO], 
		ISNULL(tmn.[Supplier Part Number], N'') AS [Supplier Part Number],
 		tmn.Year, 
		tmn.[COMPANY NAME], 
		tblSpend.[Standard Supplier Name]
 	FROM 
	(
		SELECT 
			CAST(REPLACE(REPLACE([ITEM NO], ' ', ''), '-', '') AS char(60)) AS [ITEM NO Clean], 
			[ITEM NO], 
			[COMPANY NAME], 
			Year, 
			[Supplier Part Number]
 		FROM tblMACOONew
	) AS tmn 
	RIGHT OUTER JOIN tblSpend 
		ON tmn.[ITEM NO Clean] = CAST(REPLACE(REPLACE(tblSpend.[Hussmann Item #], ' ', ''), '-', '') AS char(60))
 			AND tmn.[COMPANY NAME] = tblSpend.[Standard Supplier Name] 
	WHERE (tmn.Year = '2024')
) AS ts 
RIGHT OUTER JOIN tblSpend 
	ON ts.[Hussmann Item #] = tblSpend.[Hussmann Item #] 
	    AND ts.[COMPANY NAME] = tblSpend.[Standard Supplier Name];

the right join return the rows from the join table that match the join condition and those that do not match includes the rows with null values for the left table. see:

https://commons.wikimedia.org/wiki/File%3ASQL_Joins.svg

as we don't know the input values and expected update value we can not help. if you converted the update to a select of the part number. then supplied sample data, and and the desired result, we could help.

Answer 2

I think whoever wrote the original query might have confused the VBA IsNull and Nz functions.  The former returns a Boolean TRUE or FALSE if an expression passed into it as its argument is NULL or NOT NULL.  The latter returns a value passed into the function as its second argument, if the expression passed in as the first argument evaluates to NULL.  Otherwise it returns the return value of the expression passed into it. 

In this case changing the following line: 

    SET [Supplier Part #] = ISNULL(ts.[Supplier Part Number], '') 

to: 

    SET [Supplier Part #] = NZ(ts.[Supplier Part Number], '') 

would assign the value returned at the Supplier Part Number column position to the Supplier Part Number # column, if it is NOT NULL, or a zero-length string otherwise. 

I haven't examined the rest of the SQL statement for possible errors, but the following line springs out: 

    ISNULL(tmn.[Supplier Part Number], N'') AS [Supplier Part Number] 

It looks to be also confusing the IsNull and Nz functions, though I'm not sure what is intended to be returned if the Supplier Part Number column position IS NULL.  Possibly a zero-length string again, if the N character is simply a typo. PS: I notice that this is not JET/ACE SQL, so maybe the ISNULL function differs here??

Answer 3

Hi @Lopez, Ahiezer

It should be updating the field "Supplier Part Number" in the table "tblSpend" but it isnt doing that.

It might be there is no data need to be updated.

And it is difficult to guess the business logic without knowing the definition of each table.

It is suggested to split this complex update statement into multiple select statements from the inside to the outside to understand the meaning of joins or subquery.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".