Update Table Using Stored Procedures Problem

[Kenos]

Hello Everyone

Can anyone help me with this?
As you can see with the SP below I am updating
dbo.Product.CustomerPrice
With a value that I am pulling from
dbo.RawProducts.CustomerPrice

When I try to execute this SP
I am getting this error
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
The statement has been terminated.


I am having a Brain Freeze with this.

Thanks in Advance.


CREATE PROCEDURE [update_Product_2]

AS
Update dbo.Product

SET dbo.Product.CustomerPrice = (SELECT TOP 100 PERCENT dbo.RawProducts.CustomerPrice
FROM dbo.Product INNER JOIN
dbo.RawProducts ON dbo.Product.PartNo = dbo.RawProducts.PartNo
WHERE (dbo.Product.PartNo IS NOT NULL) AND (dbo.RawProducts.PartNo IS NOT NULL) OR
(dbo.RawProducts.CustomerPrice > 0) AND (LEN(dbo.Product.PartNo) > 0))
GO

 

[tlbroadbent]

The correct syntax is...

CREATE PROCEDURE [update_Product_2]

AS

Update dbo.Product
SET CustomerPrice = dbo.RawProducts.CustomerPrice
FROM dbo.Product
INNER JOIN dbo.RawProducts
ON dbo.Product.PartNo = dbo.RawProducts.PartNo
WHERE dbo.RawProducts.CustomerPrice > 0
AND LEN(isnull(dbo.Product.PartNo,'')) > 0
AND dbo.Product.CustomerPrice <> dbo.RawProducts.CustomerPrice If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Kenos]

Thanks tlbroadbent

This works great