I want to replace fields with STRING with correct data type FLOAT64 in BigQuery. How do I do it?

Question

I am new to learning Data Analysis. I have a field in my BigQuery dataset named Unemployment_rate that should be FLOAT64. However, some fields that should be 0 are represented as 'NaN', which is STRING. How do I use the function UPDATE TABLE to update the table to be consistent with other cells under that field as a FLOAT64?

UPDATE 
  `personal-portfolio-397412.capstone_project.2023_HY_Top_950_Youtubers`
SET Unemployment_rate = 0.0
WHERE Unemployment_rate = 'NaN'

I expected the rows under Unemployment_rate field filled as 'NaN' to be standardized to the correct floating-point data type and replaced with 0.0.

I got the error message

no matching signature for operator = for argument types: FLOAT64, STRING. Supported signature: ANY = ANY at [4.7].

I also tried using the ALTER TABLE/ COLUMN and even the CASE statement but I just can't seem to get past the error message.

I'd appreciate help from more experienced analysts who are familiar with BigQuery syntax, pls.

Answer 1

Try

WHERE IS_NAN(Unemployment_rate)

Answer 2

You might want try

UPDATE 
  `personal-portfolio-397412.capstone_project.2023_HY_Top_950_Youtubers`
SET Unemployment_rate = 0.0
WHERE Unemployment_rate IS NULL;