How to INSERT a New Record or UPDATE One if It Already Exists in SQLite

1. SQLite INSERT Command
2. SQLite UPDATE Command
3. SQLite INSERT a New Record or UPDATE One if It Already Exists

This article explains how to use the INSERT, UPDATE, and INSERT OR UPDATE commands. This also discusses how to INSERT a new record or UPDATE a record if it already exists.

SQLite INSERT Command

In SQLite, inserting data into the table is pretty simple. You can use the INSERT statement to get the task done.

In SQLite, various forms of the INSERT statement allow you to insert multiple rows, single rows, and default values into the table. You can insert a row of data into the table by using the SELECT statement.

To insert a single row into a database table, you can use the following form of the INSERT statement:

INSERT  INTO  table (c1,c2, c3..) VALUES( val1, val2,val3..);

Before adding, you need to specify the name of the table you intend to insert the data to after the INSERT INTO statement.

For columns of the table, you need to add a comma-separated list. The list is optional but writing it down is good practice.

Add a separate comma list of values after the VALUE keyword in the same order of columns. If you choose to omit the column list, you will have to specify values for all columns in the value list.

Note that the number of columns and values in the list should be the same.

You may use a similar format to insert multiple rows into a table. You can do this in the following manner with the INSERT statement:

INSERT  INTO table1 (c1,c2,c3..) VALUES (val1,val2,val3...), (val1,val2,val3...), ... (val1,val2,val3...), ;

Every value list written after the VALUES clause will be inserted into the table as a row.

In the previous example, add three rows to the table. Once this is done, SQLite issues a message "Row Affected: 3".

This means that the data in three rows have been modified. You can use the select statement to view the table’s contents to ensure your intended insertion is in the database.

You can also specify and enter default values into a table. This inserts a new row in the table using the default values previously specified for you.

NULL is assigned if no default value is available and the NOT NULL constraint is not specified.

SQLite UPDATE Command

If you need to modify data in SQLite, use the UPDATE query. To update selected rows, you can use the WHERE query with the UPDATE query; otherwise, all the table rows will be updated.

The syntax for updating data in SQLite is as follows:

UPDATE table_name
SET c1 = val1, c2 = val2...., cName = valName
WHERE [your condition];

You can combine the AND or the OR operators if you need multiple conditions met. You don’t have to use the WHERE clause to update all the table rows.

SQLite INSERT a New Record or UPDATE One if It Already Exists

The INSERT OR UPDATE command is essentially an extension of the INSERT command. The major difference is that if the row being inserted does not exist, INSERT OR UPDATE performs an INSERT operation.

However, if the row inserted already exists, INSERT OR UPDATE performs an UPDATE operation. This operation updates the row with specific column values.

This update occurs when the specified data values are identical to the existing data. INSERT OR UPDATE uses the same syntax, features, and restrictions employed by the INSERT statement.

If you need to add a record with a unique value, the field is modified if the value, for example, name, already exists. To do this, you can use the following code:

insert or replace into Employee (ID, Name, Type, Age, Salary) values
((select ID from Employee where Name = "SearchName"), "SearchName", ...);

Any field that doesn’t exist in the insert list will be assigned NULL if the table row already exists. Hence, we use the subselect for the ID column here.

In the replacement case, this statement would set the value to NULL, and then a new ID would be allocated.

A similar approach can be used if you want to leave specific field values alone in the row in the replacement case and set the field value to NULL in the insert case.

Let’s assume we want to leave the Salary column alone from the previous example. This can be achieved in the following manner:

insert or replace into Employee (ID, Name, Type, Age, Salary) values (
   (select ID from Employee where Name = "SearchName"),
   "SearchName",
    5,
    6,
    (select Salary from Book where Name = "SearchName"));

Another approach for this can be to use INSERT OR IGNORE followed by UPDATE.

For example: (Here, name is the primary key.)

INSERT OR IGNORE INTO Employee (name, age) VALUES ('Janet,' 23)
UPDATE Employee SET age = 23 WHERE name='Janet'

Here, the first command will insert the record. If the record exists, this will ignore the error caused by the conflict with the existing primary key.

The second command here will update the record, which in this case, now exists.

SQLite employs several commands that allow users to insert, modify and extract data as required. The INSERT and UPDATE commands are crucial in getting the job done.

There are various alterations as well as approaches involved in using these commands. In this article, we outlined the syntax and functionality of these commands to ensure you know how to use them.