How to check for matching in SQLite in order to insert new data or update them?

Question

I want to insert data into a table if no matching value for column EqpCode exists. If there were any matches, I want to update that row. I used this query but it has syntax error near WHERE:

INSERT INTO FileInfo(EqpCode, EqpName, FileName) VALUES ('123', 'test01', 'test02') 
WHERE NOT EXISTS (SELECT 1 FROM FileInfo WHERE EqpCode='123')

Answer 1

First, you must define the column EqpCode to be UNIQUE (or the PRIMARY KEY if there isn't one in the table):

CREATE TABLE FileInfo(
  id INTEGER PRIMARY KEY,
  EqpCode TEXT UNIQUE, 
  EqpName TEXT, 
  FileName TEXT
);

Then use SQLite's UPSERT:

INSERT INTO FileInfo(EqpCode, EqpName, FileName) VALUES ('123', 'test01', 'test02')
ON CONFLICT(EqpCode) DO UPDATE 
SET EqpName = EXCLUDED.EqpName,
    FileName = EXCLUDED.FileName;

This way, the new row will be inserted only if '123' does not exist in the table.
If it does exist, the row will be updated with the provided values of EqpName and FileName.

See the demo.