Why does my BigQuery UPDATE fail when replacing 0 values with NULL using CASE statements?

Unfollow Follow

Kapil

Updated 8 hours ago in

I’m cleaning a weather dataset in Google BigQuery where missing values in wind_speed and visibility were incorrectly stored as 0.

I tried updating the table like this:

UPDATE `my_project.weather_data.tracks_2023`

SET

wind_speed = CASE WHEN wind_speed = 0 THEN NULL END,

visibility = CASE WHEN visibility = 0 THEN NULL END

WHERE wind_speed = 0 OR visibility = 0;

The query returns an error.

My goal is simple:

Convert 0 values in wind_speed to NULL
Convert 0 values in visibility to NULL
Leave all other values unchanged

What’s the correct way to do this in BigQuery?

<div class="relative basis-auto flex-col -mb-(--composer-overlap-px) pb-(--composer-overlap-px) [--composer-overlap-px:28px] grow flex">
<div class="flex min-h-0 grow flex-col text-sm">
<div class="qMYqUG_convSearchResultHighlightRoot">
<div class="" data-turn-id-container="request-6a797470-b9b0-83ee-82eb-ded5833ee95f-14" data-is-intersecting="true">
<section class="text-token-text-primary w-full focus:outline-none has-data-writing-block:pointer-events-none [&:has([data-writing-block])>*]:pointer-events-auto R6Vx5W_threadScrollVars scroll-mb-[calc(var(--scroll-root-safe-area-inset-bottom,0px)+var(--thread-response-height))] scroll-mt-[calc(var(--header-height)+min(200px,max(70px,20svh)))]" dir="auto" data-turn-id="request-6a797470-b9b0-83ee-82eb-ded5833ee95f-14" data-turn-id-container="request-6a797470-b9b0-83ee-82eb-ded5833ee95f-14" data-testid="conversation-turn-82" data-turn="assistant">
<div class="text-base my-auto mx-auto pb-8 [--thread-content-margin:var(--thread-content-margin-xs,calc(var(--spacing)*4))] @w-sm/main:[--thread-content-margin:var(--thread-content-margin-sm,calc(var(--spacing)*6))] @w-lg/main:[--thread-content-margin:var(--thread-content-margin-lg,calc(var(--spacing)*16))] px-(--thread-content-margin)">
<div class="[--thread-content-max-width:40rem] @w-lg/main:[--thread-content-max-width:48rem] mx-auto max-w-(--thread-content-max-width) flex-1 group/turn-messages focus-visible:outline-hidden relative flex w-full min-w-0 flex-col agent-turn" data-conversation-screenshot-content="">
<div class="flex max-w-full flex-col gap-4 grow">
<div class="min-h-8 text-message relative flex w-full flex-col items-end gap-2 text-start break-words whitespace-normal outline-none keyboard-focused:focus-ring [.text-message+&]:mt-1" dir="auto" data-message-author-role="assistant" data-message-id="7a1f75fa-b118-43f5-b2e9-0b23d5a3eeca" data-turn-start-message="true" data-message-model-slug="gpt-5-6">
<div class="flex w-full flex-col gap-1 empty:hidden">
<div class="markdown prose dark:prose-invert wrap-break-word w-full light markdown-new-styling">
<p class="PDq2pG_selectionAnchorContainer" data-start="109" data-end="244">I’m cleaning a weather dataset in Google BigQuery where missing values in <code data-start="183" data-end="195">wind_speed</code> and <code data-start="200" data-end="212">visibility</code> were incorrectly stored as <code data-start="240" data-end="243">0</code>.</p>
<p data-start="246" data-end="283">I tried updating the table like this:</p>
<div class="relative w-full mt-4 mb-1">
<div class="">
<div class="contents">
<div class="border border-token-border-light border-radius-3xl corner-superellipse/1.1 rounded-3xl">
<div class="relative h-full w-full border-radius-3xl bg-(--code-block-surface) corner-superellipse/1.1 overflow-clip rounded-3xl [--code-block-surface:var(--bg-elevated-secondary)] dark:[--code-block-surface:var(--composer-surface-primary)] lxnfua_clipPathFallback">
<div class="pointer-events-none absolute inset-x-4 top-12 bottom-4">
<div class="pointer-events-none sticky z-40 shrink-0 z-1!">
<div class="sticky bg-token-border-light"> </div>
</div>
</div>
<div class="relative">
<div class="h-full min-h-0 min-w-0">
<div class="h-full min-h-0 min-w-0">
<div class="">
<div class="relative">
<div class="">
<div class="relative z-0 flex h-full min-h-0 max-w-full">
<div id="7a1f75fa-b118-43f5-b2e9-0b23d5a3eeca:0:editor" class="Rx43rG_codemirror z-10 flex h-full min-h-0 w-full flex-col items-stretch" dir="ltr">
<div class="cm-editor ͼ1 ͼ2 ͼd ͼr">
<div class="cm-announced" aria-live="polite"> </div>
<div class="cm-scroller">
<div class="cm-content" role="textbox" aria-label="Edit code" data-language="sql">
<div class="cm-line"><span class="ͼg">UPDATE</span> `my_project.weather_data.tracks_2023`</div>
<div class="cm-line"><span class="ͼg">SET</span></div>
<div class="cm-line">wind_speed <span class="ͼg">=</span> <span class="ͼg">CASE</span> <span class="ͼg">WHEN</span> wind_speed <span class="ͼg">=</span> <span class="ͼj">0</span> <span class="ͼg">THEN</span> <span class="ͼj">NULL</span> <span class="ͼg">END</span>,</div>
<div class="cm-line">visibility <span class="ͼg">=</span> <span class="ͼg">CASE</span> <span class="ͼg">WHEN</span> visibility <span class="ͼg">=</span> <span class="ͼj">0</span> <span class="ͼg">THEN</span> <span class="ͼj">NULL</span> <span class="ͼg">END</span></div>
<div class="cm-line"><span class="ͼg">WHERE</span> wind_speed <span class="ͼg">=</span> <span class="ͼj">0</span> <span class="ͼg">OR</span> visibility <span class="ͼg">=</span> <span class="ͼj">0</span>;</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="">
<div class=""> </div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<p data-start="495" data-end="522">The query returns an error.</p>
<p data-start="524" data-end="542">My goal is simple:</p>
<ul data-start="543" data-end="671">
<li data-section-id="1y0x0sc" data-start="543" data-end="589">Convert <code data-start="553" data-end="556">0</code> values in <code data-start="567" data-end="579">wind_speed</code> to <code data-start="583" data-end="589">NULL</code></li>
<li data-section-id="w9gniu" data-start="590" data-end="636">Convert <code data-start="600" data-end="603">0</code> values in <code data-start="614" data-end="626">visibility</code> to <code data-start="630" data-end="636">NULL</code></li>
<li data-section-id="pf4yvr" data-start="637" data-end="671">Leave all other values unchanged</li>
</ul>
<p data-start="673" data-end="719">What’s the correct way to do this in BigQuery?</p>
</div>
</div>
</div>
</div>
</div>
</div>
</section>
</div>
</div>
</div>
</div>
<div id="thread-bottom-container" class="sticky bottom-0 z-10 group/thread-bottom-container relative isolate w-full basis-auto has-data-has-thread-error:pt-2 has-data-has-thread-error:[box-shadow:var(--sharp-edge-bottom-shadow)] md:border-transparent md:pt-0 dark:border-white/20 md:dark:border-transparent print:hidden pointer-events-none [--thread-content-max-width:40rem] @w-lg/main:[--thread-content-max-width:48rem] Ejxyja_threadFooterContentFade">
<div>
<div class="text-base mx-auto [--thread-content-margin:var(--thread-content-margin-xs,calc(var(--spacing)*4))] @w-sm/main:[--thread-content-margin:var(--thread-content-margin-sm,calc(var(--spacing)*6))] @w-lg/main:[--thread-content-margin:var(--thread-content-margin-lg,calc(var(--spacing)*16))] px-(--thread-content-margin)">
<div>
<div class="[--thread-content-max-width:40rem] @w-lg/main:[--thread-content-max-width:48rem] mx-auto max-w-(--thread-content-max-width) flex-1 mb-(--thread-component-gap,24px) pointer-events-auto">
<div class="w-full">
<div class="pointer-events-auto relative z-1 flex h-(--composer-container-height,100%) max-w-full flex-(--composer-container-flex,1) flex-col">
<div class="relative">
<div class="bg-(--composer-surface-primary) cursor-text overflow-clip bg-clip-padding px-2 py-[9px] contain-inline-size motion-safe:transition-colors motion-safe:duration-200 motion-safe:ease-in-out group-not-data-expanded/composer:min-h-[52px] group-not-data-expanded/composer:py-[5px] grid grid-cols-[auto_1fr_auto] [grid-template-areas:'header_header_header'_'leading_primary_trailing'_'._footer_.'] group-data-expanded/composer:[grid-template-areas:'header_header_header'_'primary_primary_primary'_'leading_footer_trailing'] max-sm:[grid-template-areas:'header_header_header'_'primary_primary_primary'_'leading_footer_trailing'] shadow-short-composer max-sm:not-dark:shadow-[0_0_0_1px_rgba(0,_0,_0,_0.04),0_2px_8px_0_rgba(0,_0,_0,_0.04),0px_4px_40px_8px_rgba(0,_0,_0,_0.025)]" data-composer-surface="true">
<div class="-my-2.5 flex overflow-x-hidden ps-1.75 pe-1.5 [grid-area:primary] group-data-expanded/composer:mb-0 group-data-expanded/composer:ps-2.5 group-data-expanded/composer:pe-2.5 min-h-14 items-center">
<div class="wcDTda_prosemirror-parent text-token-text-primary max-h-[max(30svh,5rem)] max-h-52 min-h-[var(--deep-research-composer-extra-height,unset)] flex-1 scroll-py-4 overflow-auto [scrollbar-width:thin] default-browser vertical-scroll-fade-mask group-data-[expanded-composer-mode-button]/composer:pe-9">
<div id="prompt-textarea" class="ProseMirror" role="textbox" aria-label="Chat with ChatGPT" data-virtualkeyboard="true"> </div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>

Cancel

Data Analysis