Set column values based on condition statement

Question

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 < alight_stage THEN service_direction = '1'
ELSE '0' END;

Answer 1

The correct syntax is:

UPDATE <table_name>
SET <column_name> = <expression> ;

Other minor improvements:

• The case expression also needs CASE WHEN <boolean_expression> ...
• The updated column is boolean so you should output TRUE or FALSE (it will work with 1s and 0s but there is no better than your code being clear).

In your case:

UPDATE smartcardtable
SET service_direction =
 CASE WHEN board_stage < alight_stage
 THEN TRUE
 ELSE FALSE
 END
 ;

If the two other columns (board_stage and alight_stage) are not nullable, you can replace the case expression with a more simple boolean expression (parentheses added only for clarity):

UPDATE smartcardtable
SET service_direction = (board_stage < alight_stage)
 ;

Answer 2

The syntax for case is not correct and the datatype is Boolean, so you must set it as True or False

CREATE TABLE smartcardtable ("ID" INT, "board_stage" int,"alight_stage" int )

INSERT INTO smartcardtable VALUES (1,1,2)

ALTER TABLE smartcardtable
ADD service_direction boolean;

UPDATE smartcardtable
SET service_direction = 
CASE WHEN board_stage < 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<>fiddle here

• Nuala Carvill: Ah so I was missing the '='. This is extremely helpful, thank you.
• ypercubeᵀᴹ: @NualaCarvill correct, you missed the =. You also had THEN service_direction = '1' inside the CASE expression which should be just THEN '1' or THEN TRUE as nbk notes.