Completely new to SQL so sorry if this code is way off. Trying to add two new fields to an existing table (Species) in SQLite. Then updating that table by setting the values for the new fields based on values from another table (temp) using the key "Id". See code below. Using SQLite v 3.33.0.
ALTER TABLE Species
ADD Name1 TEXT, ScientificName1 TEXT;
UPDATE Species
SET Name1 = temp.Name
,ScientificName1 = temp.ScientificName
FROM temp
WHERE temp.Id = Species.Id;
The ALTER TABLE statement does not support multiple columns, so you must add them in 2 separate statements:
ALTER TABLE Species ADD Name1 TEXT;
ALTER TABLE Species ADD ScientificName1 TEXT;
Your UPDATE statement is correct if your version of SQLite is 3.33.0+.
For previous versions (3.15.0+) you could use ROW VALUES:
UPDATE Species
SET (Name1, ScientificName1) = (
SELECT temp.Name, temp.ScientificName
FROM temp
WHERE temp.Id = Species.Id
);
See the demo.
