Ashkan - 6 months ago 27

SQL Question

I am going to find the best coefficient

`(Z)`

`[Target] = Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))`

I have about 100,000 rows in my data-set which do not meet the

`Target`

`Z`

`Z=34.5`

`Z=13.5`

`Z`

I wrote code like this:

`While (Select [Target] From dbo.product) < 1000`

Begin

Update dbo.product

SET [Z] = Z + 0.5

Update dbo.product

SET [Target] = Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))

IF (Select [Target] From dbo.product) > 1000

Break

Else

Continue

End

Once I run this query, I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Do you have any idea how can I fix this error? And also do you believe this code could solve my problem? I appreciate your help!

Answer

As you want to make sure all rows with `[Target] >= 1000`

, use `MIN`

to find the row with smallest value.

```
While (Select MIN([Target]) From dbo.product) < 1000
Begin
Update dbo.product
SET [Z] = Z + 0.5
WHERE [Target] < 1000
Update dbo.product
SET [Target] = Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))
IF (Select MIN([Target]) From dbo.product) > 1000
Break
Else
Continue
End
```