"Chris2" <rainofsteel.NOTVALID@GETRIDOF.luminousrain.com> wrote in message
news:OpydnUAcKJCZ2zLenZ2dnUVZ_t6dnZ2d@comcast.com...
>
> "Vishal" <vrajput77@hotmail.com> wrote in message
> news:es9pBlbCGHA.2320@TK2MSFTNGP11.phx.gbl...
>> Hi,
>>
>> I am trying to update one field in a table from the existing
> values in the
>> table & have been getting an error since the past 4 years, have
> tried a lot
>> of things but it just does't seem to work. Have I had enough for
> the day or
>> is it just SQL. Here is the update query
>> The field types are
>> CSVAL MONEY DEFAULT 0
>> CurrYr MONEY DEFAULT 0
>> PrevYr MONEY DEFAULT 0
>>
>> Query :
>>
>> UPDATE tblMnthCompSales
>> SET CSVal =
>> (SELECT ((CurrYr-PrevYr)/PrevYr) from tblMnthCompSales
>> WHERE CSales = 1 AND PrevYr > 0)
>>
>> ERROR :
>>
>> Subquery returned more than 1 value. This is not permitted when
> the subquery
>> follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
>> Warning: Null value is eliminated by an aggregate or other SET
> operation.
>> The statement has been terminated.
>>
>> Thanks
>>
>
> Vishal,
>
> Try running the above subquery by itself:
>
> SELECT ((CurrYr-PrevYr)/PrevYr) from tblMnthCompSales
> WHERE CSales = 1 AND PrevYr > 0
>
> How many records does it return?
>
> If it returns more than one (as is suggested by the error message),
> then the subquery will have to be modified until it only returns one
> record.
>
>
> Sincerely,
>
> Chris O.
>
>

"Vishal" <vrajput77@hotmail.com> wrote in message
news:es9pBlbCGHA.2320@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am trying to update one field in a table from the existing
values in the
> table & have been getting an error since the past 4 years, have
tried a lot
> of things but it just does't seem to work. Have I had enough for
the day or
> is it just SQL. Here is the update query
> The field types are
> CSVAL MONEY DEFAULT 0
> CurrYr MONEY DEFAULT 0
> PrevYr MONEY DEFAULT 0
>
> Query :
>
> UPDATE tblMnthCompSales
> SET CSVal =
> (SELECT ((CurrYr-PrevYr)/PrevYr) from tblMnthCompSales
> WHERE CSales = 1 AND PrevYr > 0)
>
> ERROR :
>
> Subquery returned more than 1 value. This is not permitted when
the subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> Warning: Null value is eliminated by an aggregate or other SET
operation.
> The statement has been terminated.
>
> Thanks
>

"Chris2" <rainofsteel.NOTVALID@GETRIDOF.luminousrain.com> wrote in message
news:AqednYHhf95p_DLeRVn-qQ@comcast.com...
>
> "Vishal" <vrajput77@hotmail.com> wrote in message
> news:Ovp7bzbCGHA.4004@tk2msftngp13.phx.gbl...
>> The query returns all the rows is what I want to do,
>
> Vishal,
>
> I am not sure what to do then. You want the subquery to return all
> rows, and SQL Server is not going to allow it.
>
>
>
>> The table structure is
>> sid INT,
>> StoreNumber INT,
>> CurrYr MONEY DEFAULT 0,
>> PrevYr MONEY DEFAULT 0,
>> CSales BIT DEFAULT 0,
>> CSVal MONEY DEFAULT 0,
>> YTD MONEY DEFAULT 0,
>>
>> So Basically I want to update the CSVal field with a result of
>> ((CurrYr-PrevYr)/PrevYr) where CSSales = 1 and PrevYr > 0
>>
>> Please help.
>>
>
> The original SQL:
>
> UPDATE tblMnthCompSales
> SET CSVal =
> (SELECT ((CurrYr-PrevYr)/PrevYr) from tblMnthCompSales
> WHERE CSales = 1 AND PrevYr > 0)
>
> I am not sure why the subquery is here. There is no correlation,
> and the subquerry is going to be returning data that has no direct
> association with the UPDATE table.
>
> The table structure given above is one third of what I need (thank
> you). The sample data and desired results would be the other two
> thirds.
>
> Operating only on the table structure, the following is a *guess* on
> my part (backup your data before trying it).
>
> Try:
>
> UPDATE tblMnthCompSales
> SET CSVal = (CurrYr-PrevYr)/PrevYr)
> WHERE CSales = 1 AND PrevYr > 0
>
>
>
> The link http://www.aspfaq.com/etiquette.asp?id=5006,
> is excellent when it comes to detailing how to provide
> the information that will best enable others to answer
> your questions.
>
>
> Sincerely,
>
> Chris O.
>
>

"Vishal" <vrajput77@hotmail.com> wrote in message
news:Ovp7bzbCGHA.4004@tk2msftngp13.phx.gbl...
> The query returns all the rows is what I want to do,

> The table structure is
> sid INT,
> StoreNumber INT,
> CurrYr MONEY DEFAULT 0,
> PrevYr MONEY DEFAULT 0,
> CSales BIT DEFAULT 0,
> CSVal MONEY DEFAULT 0,
> YTD MONEY DEFAULT 0,
>
> So Basically I want to update the CSVal field with a result of
> ((CurrYr-PrevYr)/PrevYr) where CSSales = 1 and PrevYr > 0
>
> Please help.
>

"Vishal" <vrajput77@hotmail.com> wrote in message
news:uRqQkIdCGHA.4040@TK2MSFTNGP12.phx.gbl...
> Hey Thank you vry much, this works :)
>
> Regards
>
>