469,898 Members | 1,687 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

sqlite insert question

131 128KB
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
3 1428
Rabbit
12,516 Expert Mod 8TB
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
divideby0
131 128KB
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
12,516 Expert Mod 8TB
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.

Similar topics

5 posts views Thread by TThai | last post: by
1 post views Thread by Mattias B | last post: by
20 posts views Thread by akej via SQLMonster.com | last post: by
11 posts views Thread by Jean-Christian Imbeault | last post: by
2 posts views Thread by Daniel Tan | last post: by
4 posts views Thread by ams | last post: by
2 posts views Thread by Russ Schneider | last post: by
9 posts views Thread by David Eades | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.