0

Is there any if-like structure in SQLite? In the below code, I tried to Insert if it is exists, or update the value if it does, but neither works:

SELECT CASE EXISTS(SELECT BuildingType FROM Building_GreatPersonPoints WHERE BuildingType = 'BUILDING_OP_BUILDING' AND GreatPersonClassType='GREAT_PERSON_CLASS_PROPHET')
    WHEN 0 THEN
        INSERT INTO Building_GreatPersonPoints(BuildingType, GreatPersonClassType, PointsPerTurn) VALUES('BUILDING_OP_BUILDING', 'GREAT_PERSON_CLASS_PROPHET', 3)
    WHEN 1 THEN
        UPDATE Building_GreatPersonPoints SET PointsPerTurn = 3 WHERE BuildingType='BUILDING_OP_BUILDING' AND  GreatPersonClassType='GREAT_PERSON_CLASS_PROPHET';

Error

near "INSERT": syntax error: SELECT CASE EXISTS(SELECT BuildingType FROM Building_GreatPersonPoints WHERE BuildingType = 'BUILDING_OP_BUILDING' AND GreatPersonClassType='GREAT_PERSON_CLASS_PROPHET') WHEN 0 THEN INSERT

INSERT INTO Building_GreatPersonPoints(BuildingType, GreatPersonClassType, PointsPerTurn) VALUES('BUILDING_OP_BUILDING', 'GREAT_PERSON_CLASS_PROPHET', 3)
WHEN NOT EXIST(SELECT * FROM Building_GreatPersonPoints WHERE BuildingType = 'BUILDING_OP_BUILDING' AND GreatPersonClassType='GREAT_PERSON_CLASS_PROPHET');

UPDATE Building_GreatPersonPoints SET PointsPerTurn = 3 WHERE BuildingType='BUILDING_OP_BUILDING' AND  GreatPersonClassType='GREAT_PERSON_CLASS_PROPHET';

Error

near "WHEN": syntax error: INSERT INTO Building_GreatPersonPoints(BuildingType, GreatPersonClassType, PointsPerTurn) VALUES('BUILDING_OP_BUILDING', 'GREAT_PERSON_CLASS_PROPHET', 3) WHEN

Please tell me the problem and how should I solve it? If possible, please tell me the generic conditional structure (for example, do A and B and C if true, do C and D if false).

Luke Vo
  • 17,859
  • 21
  • 105
  • 181
  • 1
    Assuming buildingtype and greatpersonclasstype are a unique index or primary key, [UPSERT](https://www.sqlite.org/lang_UPSERT.html). – Shawn Feb 24 '19 at 10:42
  • @Shawn yes they are. thanks for the tips, this should fix my problem. However if possible, is there any structure statement to do more generic cases? – Luke Vo Feb 24 '19 at 10:43
  • In my specific case, UPSERT is too new and my DBMS didn't support it, this one can help: https://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update – Luke Vo Feb 24 '19 at 16:38

0 Answers0