1

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;
0

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)
 ;
1
  • This is extremely helpful, thank you. Feb 15 '21 at 11:43
1

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

2
  • Ah so I was missing the '='. This is extremely helpful, thank you. Feb 15 '21 at 11:43
  • @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. Feb 15 '21 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.