CODEWITHSUNDEEP
sql-server
Jaakko Seppälä
Jaakko Seppälä

Reputation: 754

Update wrong data to the average of a table

I have a table of data that should be integers from 1 to 7. But the data contained mistakes and non-numeric data so I saved the column as nvarchar-type variable. Now I would like to estimate the wrong data by the average of the correct data, i.e. if the value is not from 1 to 7, it should be updated to the average of the data in the same column where the average has been computed on those cells that have value 1,2, 3,4,5,6 or 7. The estimated value can be a float. How can I do that in MSSQL? I tried 

SELECT AVG(CAST(ky1 AS FLOAT)) FROM esimerkkikysely
WHERE NOT ISNUMERIC(ky1)=1 OR ky1 NOT BETWEEN 1 AND 7

but it returned 0.

Also, 

SELECT AVG(CAST(ky1 AS FLOAT)) FROM esimerkkikysely
WHERE ISNUMERIC(ky1)=1

returns about 4.643.

Upvotes: 1

Views: 68

Answers (2)

LunarSage
LunarSage

Reputation: 285

Try this. Please, PLEASE do all your updates in a new column (I've called it KY2 in the code below). The last thing you want to do is destroy the data you are working from, even if it is filled with errors.

UPDATE esimerkkikysely 
SET    KY2 = CASE WHEN LTRIM(RTRIM(KY1)) IN ('1','2','3','4','5','6','7')
                 THEN CONVERT(FLOAT, KY1)
             ELSE (SELECT AVG(CONVERT(FLOAT, KY1))
                   FROM   esimerkkikysely e
                   WHERE  LTRIM(RTRIM(KY1)) IN ('1','2','3','4','5','6','7')) END
WHERE  LTRIM(RTRIM(KY1)) NOT IN ('1','2','3','4','5','6','7')

I added in the TRIM as if the data import is as bad as you suggest, the chances of spaces being imported and messing up the comparison seems quite high.

Upvotes: 1

KtX2SkD
KtX2SkD

Reputation: 752

You only want the average of integers, between 1 and 7 inclusive, with tolerance to NULLs and strings, correct?

DECLARE @T1 TABLE (SuperColumn VARCHAR(30))
INSERT INTO @T1 VALUES ('2'), ('9874859'), ('JACKJACKSON'), ('1'), ('2'), ('2'), ('1'), ('3')

SELECT AVG(HisHighnessConverted)
FROM ( -- Do AVG only after filtering out problematic values.
    SELECT CONVERT(float, SuperColumn) AS HisHighnessConverted
    FROM @T1
    WHERE TRY_CONVERT(float, SuperColumn) BETWEEN 1 AND 7 -- Skips NULLs, failed converts, and successes outside of the BETWEEN range.
) AS T

Upvotes: 0

Related Questions