How to create a WHILE loop in SQL and avoid Subquery error

Question

I am going to find the best coefficient (Z) for the following equation:

[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 level. So, I am going to find the minimum Z value to pass the target level. Each row is identical so one row might need Z=34.5 and another one might need Z=13.5. I want to write a code to examine each row through a loop until all rows meet the desired target level and print the optimum Z values for all rows.

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!

`(Select [Target] From dbo.product)` returns a recordset and you can't compare a recordset with a integer. — ydoow, Sep 01 '16 at 04:43
@ydoow Thank you for your comment. Actually I saw Microsoft example for While function and there was almost same comparison. `(SELECT AVG(ListPrice) FROM Production.Product) < $300` — Ashkan Mirzaee, Sep 01 '16 at 06:03
Note in your example `SELECT AVG(ListPrice)` always return a single value, rather than a record set. — ydoow, Sep 01 '16 at 06:05
If you're sure there's only single row in the table, you can use `SELECT TOP 1 [Target] From dbo.product` to make sure it's taking a single value for comparison. — ydoow, Sep 01 '16 at 06:07
@ydoow You are right the AVG refer to a single value. My data-set in not include only one row. So how can I create a loop in this case? — Ashkan Mirzaee, Sep 01 '16 at 06:15
@Ashkan so what do you want to use to compare against `1000`? Is that the number of record / the average value of all `Target` column / the maximum or minimum value of `Target`? Or you want to iterate over each row? — ydoow, Sep 01 '16 at 06:27
@ydoow I want to compare each row in the `Target` column with `1000`. Does that make sense? — Ashkan Mirzaee, Sep 01 '16 at 06:35
@ydoow I need a loop update on all records until `Target` is greater than 1000. — Ashkan Mirzaee, Sep 01 '16 at 06:47
So it should be `While (Select MIN([Target]) From dbo.product) < 1000` since you want to make sure all `[Target]` is `>= 1000` — ydoow, Sep 01 '16 at 06:50
If you're going to treat a database table like a spreadsheet, you'd be better off using a genuine spreadsheet product. Ideally, in SQL, you generate set-based solutions (i.e. here it sounds like you ought to have a table of all possible `Z` values and then *join* the two tables together with a suitable condition such that you select the lowest `Z` value that matches your criteria for each row). — Damien_The_Unbeliever, Sep 01 '16 at 07:04

Tim Biegeleisen · Answer 1 · 2016-09-01T04:54:26.287

2

You want to iterate over every record in the dbo.product table, and update the Z and Target columns based on a condition. Appreciate that the only real purpose of the loop, from what I can see, is to find a value of Z which is large enough to cause the loop to exit and make the assignments. But we can actually come up with a formula for what the value of Z should be for each record, without explicitly iterating with a loop:

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

The loop will break when [Target] exceeds 1000, which leads to the following inequality:

1000 < Z * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))

But we can solve for Z, leading to this inequality:

Z > 1000 / SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))

The only remaining trick is that Z comes in increments of 0.5, so we should therefore round up to the nearest 0.5. We can use this formula for Z to solve for the corresponding value of Target. This leads to the following code, which does not require any sort of loop:

GO
BEGIN
    UPDATE dbo.product 
    SET [Z] = FLOOR((1000 / SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2)) + 0.4) * 2) / 2
    UPDATE dbo.product 
    SET [Target] = [Z] * SQRT(Mu_L * POWER(Sigma_D,2) + POWER(Mu_D,2) * POWER(Sigma_L,2))
END

edited Sep 01 '16 at 04:54

answered Sep 01 '16 at 04:47

Tim Biegeleisen

502,043
27
286
360

Thank you very much for this smart solution. But I need to calculate some other parameters at each iteration and for this reason I need to create a loop. I will appreciate it if you could help me to figure it out. – Ashkan Mirzaee Sep 01 '16 at 05:59
Fair enough. The error you are getting is because your `SELECT` returns more than one record (all records, actually). You need to iterate over the records instead. – Tim Biegeleisen Sep 01 '16 at 06:02
Now I understood what is wrong here! Yes `select` returns all records. But do you have any idea how can I iterate over the records? – Ashkan Mirzaee Sep 01 '16 at 06:20
@Ashkan Have a look [here](http://stackoverflow.com/questions/20662356/sql-server-loop-how-do-i-loop-through-a-set-of-records). – Tim Biegeleisen Sep 01 '16 at 06:23
Thank you very much for your help! – Ashkan Mirzaee Sep 01 '16 at 06:27

ydoow · Accepted Answer · 2016-09-01T23:19:10.777

1

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

edited Sep 01 '16 at 23:19

answered Sep 01 '16 at 06:52

ydoow

2,969
4
24
40

I have a problem here. This code updating all `Z` at each iteration. It means at the end it gives a unique `Z` value for all rows. While I am looking for the minimum `Z` for each row which lead to `Target` greater than `1000`. Could you help me please to deal with this problem? – Ashkan Mirzaee Sep 01 '16 at 08:07
1

So in the first query you may only want to update the row where `[Target]` doesn't meet your target, and will be involved in the update. I've revised the answer for your reference. You may also want to put the same `WHERE` clause in the second query to have better performance. – ydoow Sep 01 '16 at 23:18
1

It seems like the `If (condition) Break Else Continue` inside the `While` loop is superfluous. It tests same condition as in the `While`. – spencer7593 Sep 01 '16 at 23:42
@spencer7593 you're right. I just keep the lines there in case if OP puts it there for a reason. – ydoow Sep 01 '16 at 23:45

How to create a WHILE loop in SQL and avoid Subquery error

2 Answers2