What is the update of the SQLite database?

AlthoughSQLiteHas more than a year of experience, but in fact there is noSQLiteAfter all, most of the time you chooseSQLiteThis kind of micro-database, table structure design is very simple, some complexSQLOperations are rarely used. On the other handSQLiteTheoretically supported2 TBWe believe thatSQLiteIn the future.

I want to talk about it todaySQLiteInUpdate.

1.TypicalUpdate(Supported)

Update

T1

Set

Column1 = V1,

Column2 = v2

Where

Key = V3;

2. update... From (Unfortunately,SQLiteIs not supported)

Update

T1

Set

Column1 = T2. column1

From

T2, T1

Where

T2.key = t1.key;

Update between tablesUpdate... FromIs required.SQLiteNo. Is there any other way? There are indeed two alternative methods:

First, SQLiteThere is a new thing in it"Insert or replace",MySQLSimilarly, this structure can be replaced if it exists and is updated if it does not exist. This mechanism can be easily implemented.Update... From.

Insert or replace

T1 (Key, column1, column2)

Select

T2.key, T2. column1, T2. column2

From

T2, T1

Where

T2.key = t1.key;

Note: To avoid insert operations, make sure that the update is performed according to the primary key. IfWhereIf the condition is not a primary key, it may be a bit difficult.

What should I do if I want to update the primary key? Is there any other way? In this case, we can onlyUpdate... WhereFor help, the following is an example:

Update

T1

Set

Column1 = (select columnx from T2 where t2.key = t1.key ),

Column2 = (select columny from T2 where t2.key = t1.key ),

Where t1.key = (select key from T2 where t2.key = t1.key );

The following is an example of a master-slave table, a department table, a member table, and the department name andCodeIs redundant information. Update the redundant information in the member table with the department name and code in the department table:

Update

Userlogins

Set

Deptname = (select deptname from attributes where attributes. [deptid] = userlogins. [deptid]),

Deptcode = (select deptcode from attributes where attributes. [deptid] = userlogins. [deptid])

Where

userlogins. [deptid] = (select [deptid] From orders where orders. [deptid] = userlogins. [deptid]);