How to properly use conditional expressions?

[Thalassicus]

I need to insert a row into a table if a value exists in another table. How do I do this? Based on reading the sqlite reference materials I tried these two methods, but neither one worked:

CASE WHEN EXISTS (SELECT * FROM Improvements WHERE Type="IMPROVEMENT_TERRACE_FARM") THEN
    INSERT INTO Improvement_TechFreshWaterYieldChanges (ImprovementType, TechType,YieldType, Yield)
    VALUES('IMPROVEMENT_TERRACE_FARM', 'TECH_CIVIL_SERVICE', 'YIELD_FOOD', '1');
    INSERT INTO Improvement_TechNoFreshWaterYieldChanges (ImprovementType, TechType,YieldType, Yield)
    VALUES('IMPROVEMENT_TERRACE_FARM', 'TECH_FERTILIZER', 'YIELD_FOOD', '1');
END

INSERT INTO Improvement_TechFreshWaterYieldChanges (ImprovementType, TechType,YieldType, Yield)
VALUES('IMPROVEMENT_TERRACE_FARM', 'TECH_CIVIL_SERVICE', 'YIELD_FOOD', '1')
WHERE EXISTS (SELECT * FROM Improvements WHERE Type="IMPROVEMENT_TERRACE_FARM");

INSERT INTO Improvement_TechNoFreshWaterYieldChanges (ImprovementType, TechType,YieldType, Yield)
VALUES('IMPROVEMENT_TERRACE_FARM', 'TECH_FERTILIZER', 'YIELD_FOOD', '1');
WHERE EXISTS (SELECT * FROM Improvements WHERE Type="IMPROVEMENT_TERRACE_FARM");

 

[MouseyPounds]

I don't know much about CASE but in examples I've seen it's never wrapped around an entire statement. As for your second option, use SELECT instead of VALUES since SELECT will let you apply the conditional:

INSERT INTO Improvement_TechFreshWaterYieldChanges (ImprovementType, TechType,YieldType, Yield)
SELECT 'IMPROVEMENT_TERRACE_FARM', 'TECH_CIVIL_SERVICE', 'YIELD_FOOD', '1'
WHERE EXISTS (SELECT * FROM Improvements WHERE Type="IMPROVEMENT_TERRACE_FARM");

INSERT INTO Improvement_TechFreshWaterYieldChanges (ImprovementType, TechType,YieldType, Yield)
SELECT 'IMPROVEMENT_TERRACE_FARM', 'TECH_FERTILIZER', 'YIELD_FOOD', '1'
WHERE EXISTS (SELECT * FROM Improvements WHERE Type="IMPROVEMENT_TERRACE_FARM");

 

[MdREV]

The code provided by MouseyPounds could work, although you may need to specify a "from" table in the first select statement (in Oracle, which is where most of my SQL experience lays, you would use SELECT ... FROM DUAL WHERE EXISTS...) It is important that either this table has a single row, or you add some additional clause to ensure it always returns a single row.

To elaborate on his solution, your issue was that you don't have a "where" clause in an insert statement, and restructuring it as a select allows you to embed a where clause.

Also, as MouseyPounds stated, CASE is used within an SQL statement for complex column expressions, and cannot wrap a statement entirely. pl/SQL is designed for this purpose, but isn't available for us to use on a SQLite database.

Edit: Did some quick research and discovered that the FROM clause is indeed optional in SQLite, so the query provided above should work as is.