A detailed explanation of the use of Replace statements in Sqlite3

Because of their own ignorance, but also because of previous projects, very little involved in the development of the database module, so that a few days ago that the SQLite database also supports the Replace statement. This article mainly explains the behavior of the Replace statement in the SQLite, also is the study notes. In addition, the Replace statement and the UPDATE statement have a similar place, but there are many differences. This article also compares the Replace statement and the UPDATE statement in SQLite.

In this example, use the following database table:

(Figure 1)

The table is named student and stores student information. The data type for all fields is text. where ID and name are used as composite primary keys. The email field adds a unique constraint. The table statement is as follows:

?

1 2 3 4 5 6 7 8 9 10

CREATE TABLE IF not EXISTS student ("id" text, "name" text is not NULL, "sex" text, "Emai L "Text UNIQUE," Fenshu "text CHECK (Fenshu > 0)," tecid "text REFERENCES teacher (ID)," class "TEXT, PRIMARY KEY (ID, name))

Behavior of the Replace statement

The 1 Replace statement deletes the original record and inserts a new record to replace the original record.

To validate this conclusion, open the SQLite command line below and execute the following statement to replace the record with ID 2.

?

1 2	Sqlite> replace into student (ID, name, sex, email, Fenshu, tecid, Class) VALUES (' 2 ', ' Lisi ', ' *f ', ' 123456@qq.com ', ' 80 ', ' 2 ', ' 1 ');

After this statement is executed, the data in the student table is shown in the following illustration:

(Figure 2)

Comparing Figure 1 and Figure 2, you can see that in Figure 1, the record with ID 2 is the first record in the table, and when the above replace statement is executed, the record with 2 is at the end of the entire table. This means that the Replace statement deletes the original record with ID 2 and inserts a new record with ID 2.

2 typically replaces all columns of a record with the Replace statement, and if a column is not specified in the Replace statement, the value of the column is empty after replace.

Let's do the experiment with ID 2, and execute the following statement:

?

1 2	Sqlite> replace into student (ID, name, sex, email, fenshu, tecid) VALUES (' 2 ', ' Lisi ', ' *f ', ' 123456@qq.com ', ' 80 ', ' 2 ');

The statement also replaces the record with ID 2, name Lisi, except that the class column is not specified when the column is specified. After the execution is complete, the data in the table is as follows:

(Figure 3)

Compared to Figure 2 and Figure 3, you can see that the class field of the record with ID 2, name Lisi, has no value.

3 Replace to determine which record is replaced according to the primary key

In the table, specify the ID and name as the composite primary key. When the above two statements are executed, ID 2 is specified in values and name is Lisi. The result that you see after execution is also the ID 2, and the record named Lisi is replaced. This shows that the Replace statement determines which record is replaced based on the value of the primary key.

4 Replace statement cannot locate the record to be replaced based on the WHERE clause

Execute the following statement:

?

1 2	Sqlite> replace into student (ID, name, sex, email, fenshu, tecid) VALUES (' 2 ', ' Lisi ', ' *f ', ' 123456@qq.com ', ' 80 ', ' 2 ' WHERE id = ' 2 ';

The following error will be reported:

?

1	Error:near "where": syntax error

5 If there are no records to replace when the Replace statement is executed, a new record is inserted.

In the student table, we let ID and name become compound primary keys. Here we replace the record with the Replace statement with ID, name a. As you can see in Figure 3, a record with name A is present in the table, but this record has an ID of 7 instead of 100. In other words, a record with ID, name a does not exist.

Execute the following statement:

?

1 2	Sqlite> replace into student (ID, name, sex, email, Fenshu, tecid, Class) VALUES (' m ', ' a ', ' *f ', ' 123456@qq.com ', ' 80 ', ' 2 ', ' 1 ');

After the execution is complete, the data in the table is as follows:

(Figure 4)

As you can see, a new record is inserted into the table.

6 if the newly inserted or replaced record conflicts with other records in the table, then the other record is deleted.

The 5th step above also illustrates the problem. Comparing figures 4 and 5, it is found that after inserting a record with a new ID of, name A, the record with ID 2, name Lisi, is also deleted. Why is that? We said at the beginning that the email field in the table adds a unique constraint. The email for the record with ID 2 is the same as the email in the newly inserted ID 100 123456@qq.com. This causes a unique constraint to be violated, so the record with ID 2 is deleted before the record with ID 100 is inserted.

Here's another test. Now we replace the record with ID 5, name LISI3, and replace its email with 2@163.com. The email field for the record with ID 5 in the table is also 2@163.com, which can cause a unique constraint to be violated.

Execute the following statement:

?

1 2	Sqlite> replace into student (ID, name, sex, email, Fenshu, tecid, Class) VALUES (' 5 ', ' Lisi3 ', ' F ', ' 2@163.com ', ' 80 ') , ' 2 ', ' 1 ');

After you finish executing this statement, the data in the table is as follows:

(Figure 5)

Compared to Figure 4 and Figure 5, a record with ID 5 was replaced and the email for this record was set to 2@163.com, which conflicted with the ID 6 in Figure 4, so the record with ID 6 was deleted, and there was no record of ID 5 in Figure 6.

Comparison of Replace statement and UPDATE statement

For UPDATE statements, because they are frequently used, they should be more familiar. The following is a simple statement that compares the behavior of the update and replace statements, and is no longer illustrated with specific examples.

The UPDATE statement uses the WHERE clause to locate the updated record;

The UPDATE statement can update one record at a time, or it can update multiple records, as long as the multiple records are combined with the requirements of the WHERE clause;

Update only updates the value of the field on the original record, does not delete the original record, and then inserts a record.

If no fields are specified in the UPDATE statement, the fields maintain the original values and are not empty;