Hi Joanne,
I have been working on MSSQL server and SQLite is new to me. I
usually did the following in MSQL server to check of the specific
row is existed in the table and have different action depending on
the result of the check. For example:
Create table versionTable (dbVersion varchar(20)
insert into versionTable values('6, 0, 0, 1');
Now the table is created and it has one row(6, 0, 0, 1).
I usually do the following to check the content of the table
If NOT EXISTS ( select 1 from versionTable where dbVersion = '6, 0,
0, 1')
insert into versionTable values('6, 0, 0, 2');
ELSE
update versionTable set dbVersion = '6, 0, 0, 2';
I really don't know how to convert these syntax from MSSQL server to
SQLite.
I suggest that you avoid thinking about procedural steps in an SQL
database. SQL is based in the concept of sets so you apply a single
action to a whole set or subset. Rather than check if something exists
and then choose what to do about it, I think a better approach is to
specify the subset that you want to affect, and run the action on that
subset. If the subset is empty, then nothing will happen. This also
has the advantage of fewer connections to the database, so potentially
twice as fast.
In your particular example, you seem to just want one row always in
the table, and insert if it doesn't exist, and update if it does
exist. SQLite has a built in variant of insert that will handle this,
called "insert or replace", which will replace if it would violate a
constraint such as a primary key. So, a rewrite would be something
like this:
-- Set up:
create table versionTable (ID integer primary key, dbVersion text);
insert into versionTable values(1, '6, 0, 0, 1');
-- Insert or replace:
insert or replace into versionTable values(1, '6, 0, 0, 2' );
-- Check:
select * from versionTable;
which gives:
ID dbVersion
-- --
1 6, 0, 0, 2
Does this suit your purpose?
Tom
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
