UPDATE Column values with Case and JOIN I have 2 tables "a" and "b".

Table "a"

| property | element | ply | ..

Table "b"

| property | ply | material | ..

What I want to do is to add a column to the first table called "material" and provide values based on the following rule:

if a.element &lt; 300000 write 80000 else write the value you would get from joining tables "a" and "b" on (property and ply)

What I wrote for the above is this:

ALTER TABLE a ADD COLUMN Material INTEGER
UPDATE a SET Material = CASE 
    WHEN a.Element &lt;= 300000 
        THEN 80000 
    ELSE b.Material FROM 
        a JOIN b ON (a.PCOMP = b.PCOMP AND a.Ply= b.Ply) 
    END



  I am using SQLite 3.14.1


But this does not go through. Any ideas?
Your CASE is almost correct, but to be able to do a separate join, you have to use a subquery:

UPDATE a
SET Material = CASE 
                 WHEN Element &lt;= 300000 
                 THEN 80000 
                 ELSE (SELECT b.Material
                       FROM b
                       WHERE a.PCOMP = b.PCOMP
                         AND a.Ply = b.Ply)
               END;