December 19, 2008 at 9:59 am
I need to update a value in dbo.Products based on a value in dbo.Cost1.
Here is a query that returns the value I want:
SELECT DISTINCT COUNT(ProductId) AS ProductCount,ProductId, AVG(CONVERT(money,TotalCost)) AS 'LabourCost'
FROM dbo.Cost1
GROUP BY ProductId
ORDER BY ProductId
From the above query I want to set the dbo.Products.MaterialCost to = AVG TotalCost
My UPDATE Query is incorrect:
UPDATE dbo.Products
SET MaterialCost =
(
SELECT DISTINCT COUNT(ProductId) AS ProductCount,ProductId, AVG(CONVERT(money,TotalCost)) AS 'LabourCost'
FROM dbo.Cost1
WHERE dbo.Cost1.ProductId = Products.CrossReference
)
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 19, 2008 at 10:07 am
You are selecting more than one column in the sub-query , this will not work in an update statement you need to only select the one column that is the update..
UPDATE dbo.Products
SET MaterialCost =
(
SELECT AVG(CONVERT(money,TotalCost)) AS 'LabourCost'
FROM dbo.Cost1
WHERE dbo.Cost1.ProductId = Products.CrossReference
)
December 19, 2008 at 11:04 am
Steve's right. But if necessary, you could also write this update as...
UPDATE p
SET p.MaterialCost = x.LabourCost
FROM dbo.Products As p
JOIN (
SELECT DISTINCT COUNT(ProductId) AS ProductCount,ProductId, AVG(CONVERT(money,TotalCost)) AS 'LabourCost'
FROM dbo.Cost1
) As x
ON x.ProductId = p.CrossReference
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 19, 2008 at 12:39 pm
Thanks guys, my knowledge is improving but still lots to learn.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 20, 2008 at 3:53 am
I needed to make a slight modification (add GROUP BY) so code now reads as follows:
UPDATE p
SET p.MaterialCost = x.LabourCost
FROM dbo.Products As p
JOIN (
SELECT DISTINCT COUNT(ProductId) AS ProductCount,ProductId, AVG(CONVERT(money,TotalCost)) AS 'LabourCost'
FROM dbo.Cost1
GROUP BY Cost1.ProductId
) As x
ON x.ProductId = p.CrossReference
Great work.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply