Help with Code

Transact-SQL
1

Below is my code and I am again new to SQL so I am hitting things that I am not sure how to do. I have an input field (@USku) that is not in the table I have to update so I take that field and access another table to get the field I can use to update the correct record in the table. I have declared a variable to save that field so I can use that info to update the Quantity on Hand for that record. However I do not think the value in that saved variable is correct at the time of the update call. If I take @SaveSku and replace it with a constant the Update is executed correctly. Can anyone help and see what I can do to change my code.

declare @SaveSku varchar(50)

SELECT dbo.Inventory.SkuId, SkuCode, QuantityOnHand, LocationId, CompanyId, dbo.Sku.SkuId as SaveSku
FROM [twretPRD].[dbo].[Inventory] LEFT OUTER JOIN
[twretPRD].[dbo].[Sku] ON dbo.Inventory.SkuId = dbo.Sku.SkuId

WHERE SkuCode = @USku and LocationID = @Location

UPDATE [twretPRD].[dbo].[Inventory]
SET QuantityOnHand = QuantityOnHand - @qoh
WHERE LocationId = @Location and SkuId = @SaveSku

2

I can't see where you are ever setting the value for @SaveSku. You do select a value and name it SaveSku but they aren't the same thing; one is a variable and the other is a column name. Also, if either SkuCode or Location are in the Sku table, you are effectively performing an inner join; not a left outer join.

3

Maybe I am not understanding how to set it. I want the variable SaveSku to get the database value of dbo.Sku.SkuId.

4

But dbo.Sku.SkuId is the same value as dbo.Inventory.SkuId, because of this code:

ON dbo.Inventory.SkuId = dbo.Sku.SkuId

Is there a different column in dbo.Sku that holds a different skuid that needs updated? In straight SQL terms, what UPDATE do you actually want to.

Btw, as to the actual SQL code, first thing, always use aliases when joining tables. And always alias every column. I've added aliases to the columns below, but I had to guess at which table:

SELECT i.SkuId, i.SkuCode, i.QuantityOnHand, i.LocationId, i.CompanyId, s.SkuId as SaveSku
FROM [twretPRD].[dbo].[Inventory] i LEFT OUTER JOIN
[twretPRD].[dbo].[Sku] s ON i.SkuId = s.SkuId
WHERE i.SkuCode = @USku and i.LocationID = @Location
5

My situation is this. one of the inputs I am getting from the user (USku) does not exist in the Inventory Table so I am using that input to look in the sku table to get the field I can use to update the correct record in the inventory table which is SkuId. Which is why it would be in both tables. Maybe I am over complicating this. Here is what I want to do in English:

Input a user sku say '12345', a location say '101' and a quantity to subtract say '1'.
I need to find the record in the sku table for 12345. In that record in that table there is another field (SkuId) that also is another representation of the that same field with a value of 'ABCDEF'. That field is the only field I can use to tie the Inventory and Sku tables together. Once I have that record I need to subtract quantity from the quantity on hand field and update it. I am just missing having the correct SkuID.

6

Forget English descriptions even. Sample data would be much clearer.

7

I kinda think that you actually want to combine the two statements, a laupdate i set QuantityOnHand = QuantityOnHand - @qoh from [twretPRD].[dbo].[Inventory] i inner join [twretPRD].[dbo].[Sku] s on i.SkuId = i.SkuId and s.SkuCode = @USku where i.LocationId = @Location and i.SkuId = s.SkuIdNote that I did make it an INNER JOIN since I'm assuming that SkuCode is in the Sku table.
To answer your question of how to set the variable:SELECT @SaveSku = dbo.Sku.SkuId FROM [twretPRD].[dbo].[Inventory] LEFT OUTER JOIN [twretPRD].[dbo].[Sku] ON dbo.Inventory.SkuId = dbo.Sku.SkuIdHTH

8

Based on my code:

9

Thanks!!! That did it.