SQLite – Insert New Value into Table if It Does Not Exist

sqlite

I am just trying to teach myself SQL so appreciate any help or advice on this one.

I have a table for suppliers which has an attribute for payment method. The payment method includes but is not limited to a number of items. e.g. paypal, visa. I was thinking of having a small table for payment method containing valid options but I would like to update the table with information entered in the suppliers table if the payment method doesn't exists. How would I go about doing this?

I am using SQLite.

Thanks

Best Answer

You have a few options.

You can handle this programmatically, in the code that sends the supplier info to the database.

You can create a stored procedure that finds payment methods in the supplier info that do not exist in the payment method table, and inserts them.

You can create a BEFORE trigger on supplier info, check if the payment method exists, and add it.

That said, I would advise against automatically creating something as critical as payment methods based on user input (or even on imported files).

First, the supplier info presumably will not have more than the payment method name. You may create a record, but critical info like payment type (credit card vs. checking account mean different data that you need to collect) will be missing.

Second, something as simple as a typo will create a new, invalid payment method in your system, instead of properly forcing the invalid data to be corrected before being added to the database.

Third, you would be allowing potential maliciously bad data to be created in your system. Without proper controls, you might allow someone to actually place valid fillable orders, and charge them nothing.

Screens that allow data to be added to your core look-up tables should be tightly controlled, only accessible to users who know what to add, and how.

Related Solutions

SQLite – How to Select Non-Unique Values from One Column

You query will need DISTINCT, so it doesn't return the values multiple times:

SELECT DISTINCT a 
FROM table 
WHERE a NOT IN (SELECT a FROM table WHERE b = 3) ;

Also beware that NOT IN may return unexpected results if your columsn are nullable. It's safer to use NOT EXISTS or LEFT JOIN / IS NULL:

SELECT DISTINCT a 
FROM table AS x
WHERE NOT EXISTS (SELECT 1 FROM table AS y WHERE y.b = 3 AND y.a = x.a) ;

SQLite – Inserting Data with SELECT into New Column

You already solved your main problem!

But I want to suggest you to create a new table for sub and maybe also for fullpath, because you store the same values again and again in this row.

Something like this:

CREATE TABLE path (id integer, name varchar(100));

Then populate it with a SELECT and add the ID to the main table!

Best Answer

Related Solutions

SQLite – How to Select Non-Unique Values from One Column

SQLite – Inserting Data with SELECT into New Column

Related Question