January 28, 2010 at 5:41 pm
Here's my table and some sample data below.
CREATE TABLE [dbo].[tblTaxRate](
[RequestId] [int] NULL,
[ItemId] [int] NULL,
[RQuantity] [int] NULL,
[RUnitCost] [real] NULL,
[Tax] [real] NULL,
[RTotalCost] [real] NULL,
) ON [PRIMARY]
RequestID ItemId Rquantity RUnitCost Tax RtotalCost
100040 1 2 45.72 100.3554100.3554
100040 2 15 46.49 765.3416765.3416
100040 3 1 381.26 418.4329418.4329
100040 4 12 5.61 73.883773.8837
100051 1 20 58.9 1292.8551292.855
100065 1 2 53.96 118.4422118.4422
100065 2 1 90 98.7749998.775
100068 1 4 359.95 1580.1811580.181
100085 1 20 7.73 169.6735169.6735
100104 1 6 1.34379 8.8488578.06274
100104 2 13 2.26556 32.3238829.45228
100124 1 500 10 5487.55487.5
100126 1 1000 5 5487.55487.5
In this example the column tax and RTotalCost are the same but the rows I need to update are those that are NOT the same. I want to set RtotalCost with tax.
The statement I am trying to use is the following:
Update tblTaxRate
Set RTotalCost =
(SELECT Tax
FROM tblTaxRate
WHERE tblTaxRate.RequestId = tblTaxRate.RequestId
and tblTaxRate.ItemId = tblTaxRate.ItemId))
I get the error message below.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I will appreciate any help.
Thanks,
Jose
January 28, 2010 at 7:24 pm
josetur12 (1/28/2010)
In this example the column tax and RTotalCost are the same but the rows I need to update are those that are NOT the same. I want to set RtotalCost with tax.The statement I am trying to use is the following:
Update tblTaxRate
Set RTotalCost =
(SELECT Tax
FROM tblTaxRate
WHERE tblTaxRate.RequestId = tblTaxRate.RequestId
and tblTaxRate.ItemId = tblTaxRate.ItemId))
I get the error message below.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I will appreciate any help.
Thanks,
Jose
I might be missing something but does this work for your scenario:
Update tblTaxRate
Set RTotalCost = Tax
where RTotalCost <> Tax
January 29, 2010 at 4:59 am
Hi,
Hope the following helps.
Update <tableName> set Rcost=Tax
where Rcost<>Tax
January 29, 2010 at 8:32 am
Thank you, both solutions work.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply