Assuming three columns in the table: ID, NAME, ROLE
BAD: This will insert or replace all columns with new values for ID=1:
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Foo', 'CEO');
BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:
INSERT OR REPLACE INTO Employee (id, role)
VALUES (1, 'code monkey');
GOOD: Use SQLite On conflict clause
UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!
UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.
GOOD but tedious: This will update 2 of the columns.
When ID=1 exists, the NAME will be unaffected.
When ID=1 does not exist, the name will be the default (NULL).
INSERT OR REPLACE INTO Employee (id, role, name)
VALUES ( 1,
'code monkey',
(SELECT name FROM Employee WHERE id = 1)
);
This will update 2 of the columns.
When ID=1 exists, the ROLE will be unaffected.
When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES ( 1,
'Susan Bar',
COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
);
Say you have a table and need to rename "colb" to "col_b":
First you rename the old table:
ALTER TABLE orig_table_name RENAME TO tmp_table_name;
Then create the new table, based on the old table but with the updated column name:
CREATE TABLE orig_table_name (
col_a INT
, col_b INT
);
Then copy the contents across from the original table.
INSERT INTO orig_table_name(col_a, col_b)
SELECT col_a, colb
FROM tmp_table_name;
Lastly, drop the old table.
DROP TABLE tmp_table_name;
Wrapping all this in a BEGIN TRANSACTION;
and COMMIT;
is also probably a good idea.
Best Answer
Basically use migrations.
Some useful help on how to use migrations is available at http://wiki.rubyonrails.org/rails/pages/understandingmigrations and http://wiki.rubyonrails.org/rails/pages/UsingMigrations. A good cheatsheet that I use is also available at http://dizzy.co.uk/ruby_on_rails/cheatsheets/rails-migrations.
Basically migrations use ruby code to create your database tables for you. It is far easier (in my opinion at least) to use nice ruby code to do this rather than SQL DDL - it also does various things automatically for you (like adding
id
fields to all your tables as rails requires). You can then use rake tasks to actually apply the migrations to your database. The other major advantage that migrations give you is that they are reverseable - so your database is versioned and you can easily jump from one version to another.