• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Update query question

I have to Update 3 fields in the Nop_ProductVariant table
with the values from the "ProdOne" table, based on the SKU field which is nvarchar(100).
Being the SKU is not an "int" field, what it the best way to do the join?

UPDATE Nop_ProductVariant
SET   OldPrice =   , ProductCost =  , Downloads =  

INNER JOIN ??
WHERE ???      SKU =

SELECT SRP, Cost, DL
FROM ProdOne

0
MikeMCSD
Asked:
MikeMCSD
1 Solution
 
Bhavesh ShahLead AnalysistCommented:

UPDATE A
SET   OldPrice =   SRP, 
ProductCost =  Cost, 
Downloads =  DL
FROM Nop_ProductVariant A INNER JOIN ProdOne B
ON A.SKU = B.SKU

Open in new window


0
 
MikeMCSDAuthor Commented:
perfect, thanks
0
 
szefcykCommented:
I'm assuming SKU is in both the Nop_ProductVariant and ProdOne tables....so you could try this...

DECLARE @OldPrice varchar(30)
DECLARE @ProductCost varchar(30)
DECLARE @Downloads int
DECLARE @SKU int
DECLARE @dynSQL nvarchar(1024)

SET @SKU = 1234
SELECT @OldPrice = SRP FROM ProdOne WHERE SKU = @SKU
SELECT @ProductCost = Cost FROM ProdOne WHERE SKU = @SKU
SELECT @Downloads =  DL FROM ProdOne WHERE SKU = @SKU

SET @dynSQL = 'UPDATE Nop_ProductVariant SET OldPrice = ' + @OldPrice + ', '+
	'ProductCost = ' + @ProductCost + ', Downloads = ' + CAST(@Downloads AS varchar) +
	' WHERE SKU = ' + CAST(@SKU AS varchar)

--PRINT @dynSQL
EXEC sp_executesql @dynSQL

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now