sqlite insert question

[divideby0]

If this is the wrong forum, please forgive me - I didn't see an sqlite category.

I've got two somewhat related tables.

tbl_main
   id integer primary key autoincrement
   volume text unqiue not null default ''
   note text not null default ''
 
tbl_media
   id integer primary key autoincrement
   volume_key text not null default ''
   fname text not null default ''
   fsize text not null default ''
   fdate text not null default ''
   ftype text not null default ''
   fduration text not null default ''
   frame text not null default ''
 

the first table's fields are for a disc volume label and a user entered description for the disc.

the second table identifies the entries by the volume_key.

my question is, can I insert new entries by selecting where a stored fname doesn't exist?

An example of what I'm asking is adding files from a flash drive to the database. I then add new files to the flash drive and want to update the database with only the newly added files.

INSERT INTO tbl_media VALUES(...) WHERE SELECT FROM tbl_media fname != 'newly added file'
 

Something like that. Is that possible? If so, what is the proper syntax?

TIA

[Rabbit]

That doesn't make any sense, why would you want to insert into the table by selecting records from the table? You just end up duplicating data that you already have.

Now if what you actually want to do is merge two DIFFERENT tables by selecting the records that exist in one that doesn't exist in the other, then you need to use an outer join query.

When you outer join two queries and return just the ones where the fields that are joined are null, that gives you the non matches. For example:

SELECT t1.*
FROM oldTable AS t1
RIGHT OUTER JOIN newTable AS t2
ON t1.keyField = t2.keyField
WHERE t1.keyField IS NULL

[divideby0]

Rabbit, thank you for your time.

Basically, I'm trying to centralize my collection of data cdroms / dvds, tape backups, hard drives, etc. dating back to the 90s.

Each drive / disc / tape is a record identifed by its volume label with any number of files. I can filter the contents before adding them to the database, which could exclude some data. Later, if I decide to add the excluded files, I can rescan the device, which lists all files on the device. With so many records, I cannot recall what's been added or filtered out.

That's where the problem lies; I don't want to add existing entries... only items that may have been excluded at the time the record was originally created.

I'm not verse in SQL, but basically, if some_file doesn't exist in the record identified by volume_key, then add some_file to the record identified by volume_key.

I remember getting help several years ago with something similar using php and mysql, but I cannot recall how it was it done... maybe use count() on the SELECT or something.

The only alternative that I can think of is to query for the name first, and then insert, but I was hoping for a way to do it using one operation.

[Rabbit]

Yes, I know. That's what the solution I outlined earlier will do.