Re: [sqlite] How to check if the table has some specific values

[T&B]

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]
-----------------------------------------------------------------------------