Set column values based on condition statement I want to add values to a new column based on: if board_stage is less than alight_stage then service_direction equals 1, to indicate Inbound; if not, then service_direction equals 0, indicating Outbound.
The code I have tried is below. The main issue is that it keeps returning syntax errors before the CASE statement. I have also played around with WHERE and IF statements. However this returns the same syntax error in the same location, i.e. just before the condition statement.
Any tips? Many thanks
ALTER TABLE smartcardtable
ADD service_direction boolean;

UPDATE smartcardtable
SET service_direction
CASE board_stage &lt; alight_stage THEN service_direction = '1'
ELSE '0' END;
The syntax for case is not correct and the datatype is Boolean, so you must set it as True or False


CREATE TABLE smartcardtable (&quot;ID&quot; INT, &quot;board_stage&quot; int,&quot;alight_stage&quot; int )




INSERT INTO smartcardtable VALUES (1,1,2)




ALTER TABLE smartcardtable
ADD service_direction boolean;




UPDATE smartcardtable
SET service_direction = 
CASE WHEN board_stage &lt; alight_stage THEN  TRuE
ELSE FALSE END;


1 rows affected




SELECT * FROM smartcardtable


ID | board_stage | alight_stage | service_direction
-: | ----------: | -----------: | :----------------
 1 |           1 |            2 | t                


db&lt;&gt;fiddle here