Page 1 of 1
SQLite - Checking for a duplicate entry before adding it
Posted: Thu Oct 17, 2013 11:27 pm
by buchacho
I have a function that will add a new entry to a table in my SQLite database. I would like to add to the function some kind of SQL query that I use to check to see if I am adding a duplicate entry before doing so. Is there a query I can perform to search the table based on a few columns to return the number of matches?
Re: SQLite - Checking for a duplicate entry before adding it
Posted: Fri Oct 18, 2013 8:05 am
by bangkok
Are you saying that you are able to do an INSERT in SQL... but you don't know how to do a... SELECT ? !
Anyway. Make a simple SELECT on your column, before to do the INSERT, to check.
Code: Select all
put "SELECT * from mytable where name='"&newEntry&"'"
Some people would advise you to put your insert function in a TRY CATCH structure.
http://forums.runrev.com/phpBB2/viewtop ... f=7&t=9418
You can even add some "fuzyness" (because a "duplicate" for human, is not a always a "duplicate" for computer... spelling mistakes, typos etc.) with "LIKE" (and wildcard %), in order to make a warning for the user before the insert.
Code: Select all
put "SELECT * from mytable where name like '%"&newentry&"%'" into tSQL
Re: SQLite - Checking for a duplicate entry before adding it
Posted: Mon Oct 21, 2013 6:47 pm
by buchacho
bangkok wrote:Are you saying that you are able to do an INSERT in SQL... but you don't know how to do a... SELECT ? !
Not exactly
I like to ask here since I am not too familiar with SQL and am looking for efficient ways to input user data. It is very time consuming to design the user input aspect with LiveCode to ensure input meets strict rules for multiple fields and drop-down menus...
I found this function to be useful, where tSQL is the SELECT argument:
Code: Select all
put revQueryDatabase(tDatabaseID, tSQL) into theCursor
put revNumberOfRecords(theCursor) into numRecords