How do I insert record only if the record doesn't exist?

Question

I have a PaymentInformation table

ID   NAME       Start       End
1    Tina       1/7/2014    1/17/2014
2    Alice      3/7/2014    3/17/2014 
3    John       5/7/2014    5/17/2014 
4    Michelle   7/7/2014    7/17/2014

I'm building my SQL query dynamically, like this:

SQLiteDataBaseQueryHolder3 = "INSERT INTO " + SQLiteHelper.TABLE2_NAME + "(" +
                            "name, Start, End" +
                            ")VALUES('" +
                            tempName + "'" +
                            ",'" +start + "'" +
                            ",'" + end + "" +
                            "')" +
                            " WHERE NOT EXISTS ( SELECT * FROM " +SQLiteHelper.TABLE2_NAME +" WHERE name = '"+
                            tempName+"' AND Start = '"+Start+"')"

Which gives the following result (formatted for readability):

INSERT INTO PaymentInformation(NAME, Start, End)
VALUES('Tina','01/10/2017','2/10/2017')
WHERE NOT EXISTS (
    SELECT *
    FROM PaymentInformation
    WHERE name = 'Tina' AND duration_start = '01/10/2017'
)

Is this how it should be done? It shouldn't insert if Tina is already in the table with a Start day of 01/10/2017.

score 6 · Answer 1 · answered Oct 23 '17 at 10:09

You can do this with a unique constraint & insert or ignore.

Create table:

ironforge:tmp phil$ sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table PaymentInformation
   ...> (
   ...> ID int,
   ...> name varchar(100),
   ...> start date,
   ...> end date );
sqlite>

Add a unique constraint on name and start:

sqlite> create unique index PaymentInformation_name_start on PaymentInformation ( name, start ) ;

Add a row:

sqlite> insert or ignore into PaymentInformation values ( 1,'Phil','2017-01-01','2017-02-02' );
sqlite> select * from PaymentInformation;
1|Phil|2017-01-01|2017-02-02
sqlite>

Add another row with the same name & start date, and it gets ignored:

sqlite> insert or ignore into PaymentInformation values ( 2,'Phil','2017-01-01','2017-02-02' );
sqlite> select * from PaymentInformation;
1|Phil|2017-01-01|2017-02-02
sqlite>

How do I insert record only if the record doesn't exist?

1 Answers1