CODEWITHSUNDEEP
sqlsqlitesql-updatealter-table
Andrew15
Andrew15

Reputation: 83

SQLite - adding columns and updating them using another table

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;

Upvotes: 1

Views: 1157

Answers (1)

forpas
forpas

Reputation: 164099

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.

Upvotes: 2

Related Questions