I have some legacy data with a binary column, fish_otolith, that is not accurate. It should indicate based on separate column named fish_age. When an age is present in the fish_age column the binary column, fish_otolith, should indicate and when a null value is present in the fish_age column the binary column, fish_otolith, should not indicate.

I am struggling with writing the correct update sql statement of "if fish_age is not Null then fish_otolith is equal to 1, else if fish is null then fish_otolith is equal to 0

CodePudding user response：

If your fish_otolith is a boolean, then try this:

update ttable 
   set fish_otolith = fish_age is not null;

CodePudding user response：

The expression fish_age is not null returns a boolean value which can be cast as an integer with the result false -> 0 and true -> 1, which is the results you are looking for. So:

update fishes  
   set fish_otolith = (fish_age is not  null)::integer;

This (or any other update) however contains a potential fatal flaw: fish_otolith can be updated independent of fish_age and can contain any valid integer not just 0 or 1. This con be overcome with a slight design change. Define fish_otolith as a generated always ... derived from fish_age. (see demo here - for both implementations).

Note: Either setting by @MikeOrganek would also work.