×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Inserting multiple records into sqlite and ignore duplicates

Inserting multiple records into sqlite and ignore duplicates

Inserting multiple records into sqlite and ignore duplicates

(OP)
Hi,

I wrote the following code to create two tables, add a couple of triggers, the problem I am having is with the add_props_date trigger... when an existing record is already present in the table email i still want a record creating in the email props table, which works however the compid field is showing as -1 I need compid to have the value of hashid (the record which already exists). If i get 10 duplicate entries into the database, there will be only one record in the email table and 10 references to that in emailprops table


Hope this makes sense, here is my code:

CODE -->

CREATE TABLE email ( 
    hashid       INTEGER PRIMARY KEY, 
    emailhash  TEXT	NOT NULL UNIQUE,
	hash_date_added DATETIME
);


CREATE TABLE emailprops ( 
    propsid		INTEGER PRIMARY KEY, 
    compid	    INTEGER, 
    props_date_added	DATETIME,
    FOREIGN KEY (compid) REFERENCES email(hashid)
);


CREATE TRIGGER add_hash_date AFTER INSERT ON email  
BEGIN  
    UPDATE email SET hash_date_added = DATETIME('NOW') WHERE hashid = new.hashid;  
END;

CREATE TRIGGER add_props_date BEFORE INSERT ON email
BEGIN
   INSERT INTO emailprops(compid, props_date_added) VALUES (new.hashid, datetime('now'));
END;



INSERT INTO email ( emailhash ) VALUES ( 'hello' );
INSERT OR IGNORE INTO email ( emailhash ) VALUES ( 'hello' ); 


Here are some screen shots showing what I get:


And this is what i want (with a little creative editing):



Thanks!

RE: Inserting multiple records into sqlite and ignore duplicates

Just a guess here...
I would set the default values for the date_added fields instead of the trigger:

CREATE TABLE email ( 
    hashid          INTEGER     PRIMARY KEY, 
    emailhash       TEXT	NOT NULL UNIQUE,
    hash_date_added DATETIME    NOT NULL DFAULT DATETIME('NOW')
);

CREATE TABLE emailprops ( 
    propsid	         INTEGER PRIMARY KEY, 
    compid	         INTEGER, 
    props_date_added	 DATETIME   NOT NULL DFAULT DATETIME('NOW'),
    FOREIGN KEY (compid) REFERENCES email(hashid)
);
 
And there is nothing to increment and keep unique your Primary Keys in both tables.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inserting multiple records into sqlite and ignore duplicates

(OP)
Thanks that seems like common sense! I have revised the code as follows:

CODE -->

CREATE TABLE email ( 
    hashid       INTEGER PRIMARY KEY, 
    emailaddresshash  TEXT	NOT NULL UNIQUE,
	hash_date_added DATETIME	NOT NULL DEFAULT (DATETIME('NOW'))
);

CREATE TABLE emailprops ( 
    propsid		INTEGER PRIMARY KEY, 
    compid	    INTEGER, 
    props_date_added	DATETIME	NOT NULL DEFAULT (DATETIME('NOW')),
    FOREIGN KEY (compid) REFERENCES email(hashid)
);

CREATE TRIGGER add_index BEFORE INSERT ON email
BEGIN  
    INSERT INTO emailprops (compid) VALUES (email.hashid);
END;

INSERT INTO email ( emailaddresshash ) VALUES ( 'junk@test.com' ); 

However I now get an error when trying to add the value to compid

Quote:

SQLite3 Error 1 - no such column: email.hashid

I need the hashid from the table email inserted into table emailprops everytime an existing record is added or already present in the table email.

This is the pseudocode if it makes sense:
try to add record to table email
if record does not exist in table email then add to table email, then add record to table emailprops
if record does exist in table email then do not add record to table email, then add record to table emailprops


Thanks

RE: Inserting multiple records into sqlite and ignore duplicates

Well, you have here Parent (email) - Child (emailprops) relationship between the 2 tables. So in order to INSERT any record into emailprops table, you need to have a record in the email table. But how do you know which record in emailprops table relates to which record in email table before you insert the record into emailprops table? And which hashid value to use as the value for compid in emailprops table? I don't think you can have this logic in the definition of the tables, you need to have it in the code that deals with INSERT INTO emailprops statements.

Your 'pseudocode' logic may theoretically work if you always insert a record into the emailprops table related to the last record in email table, but is that always the case? You may have 100 records in email table, and you want to insert a record into emailprops table related to the 27th record in email table. Which hashid value do you use then?

Also, Access uses something called 'AutoNumber' field as PK for a table, Oracle and SQL Server uses sequences to increment PK value in the tables. I would assume SQLite also uses some kind of a sequence for PK

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Inserting multiple records into sqlite and ignore duplicates

(OP)
Thanks for the reply, I need to look at the autonumbering and see how that can help or look at another way completely. Thanks very much for the suggestions!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close