Calculate Percentage from two colums and write back to table

Question

With a temporary table as:

DECLARE @Results TABLE (
    CDA  varchar(60),
    Found  int default 0,
    Accepted int default 0,
    Percentage decimal(3,0) default 0.0,
)

How do you take populated Found and Accepted columns and write it back to the Pecentage column?

I have:

UPDATE @Results SET Percentage = (
    SELECT (((Accepted * 1.00) / Found) * 100) FROM @Results 
)

Which if you take the SELECT line (comment out the UPDATE) it returns the correct values. However in the context of the UPDATE it fails with Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

How do I write the values back to the correct row?

Answer 1

You need a where clause, e.g. (untested)

UPDATE Result oResult
  SET Percentage = (SELECT Accepted / Found * 100
                      FROM Result iResult 
                     WHERE iResult.cda = oResult.cda);

However, if this is calculated you may wish not to have this as a column and just add it to any queries instead. Note, most databases have a percent function.

Answer 2

UPDATE Result
   SET Percentage = Accepted / Found * 100

It won't work though with FOUND and ACCEPTED defined as INT, use cast when dividing or declare them with the same type as PERCENTAGE.