By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

sqlite insert question

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

I've got two somewhat related tables.

Expand|Select|Wrap|Line Numbers
  1. tbl_main
  2.    id integer primary key autoincrement
  3.    volume text unqiue not null default ''
  4.    note text not null default ''
  5.  
  6. tbl_media
  7.    id integer primary key autoincrement
  8.    volume_key text not null default ''
  9.    fname text not null default ''
  10.    fsize text not null default ''
  11.    fdate text not null default ''
  12.    ftype text not null default ''
  13.    fduration text not null default ''
  14.    frame text not null default ''
  15.  
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tbl_media VALUES(...) WHERE SELECT FROM tbl_media fname != 'newly added file'
  2.  
Something like that. Is that possible? If so, what is the proper syntax?

TIA
Jul 7 '13 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,347
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.*
  2. FROM oldTable AS t1
  3. RIGHT OUTER JOIN newTable AS t2
  4. ON t1.keyField = t2.keyField
  5. WHERE t1.keyField IS NULL
Jul 7 '13 #2

100+
P: 131
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.
Jul 7 '13 #3

Rabbit
Expert Mod 10K+
P: 12,347
Yes, I know. That's what the solution I outlined earlier will do.
Jul 8 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.