SQLite Data Multi-table federated update

In fact, in MySQL, multi-table joint update is not difficult.

Grammar:

1	UPDATEtable_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHEREwhere_definition]

Example:

1	UPDATEtable1, table2 SET table1.value=table2.value, a.type=b.type WHEREtable1.sid=table2.sid;

Similarly, in SQL Server, it is possible to use federation as a simple implementation:

1	UPDATEt1 SET col1=t2.col1 FROM table1 t1 INNER JOIN table2 t2 ONt1.col2=t2.col2;

However, unfortunately, in SQLite does not support such a syntax, it is not in SQLite does not support multi-table joint update it? Of course not, in fact, SQLite in the multi-table joint update can also be achieved,

First of all, SQLite has a fresh thing "INSERT OR REPLACE", similar to MySQL, this structure can be guaranteed to be replaced in the presence of the situation, the non-existent situation of the update, with this mechanism can easily implement the update ... From the.

insert or replace into t1 ( key select t2. key from t2, T1 where t2. key = t1. key TD class= "Code" >

1

This approach avoids insertions, first ensuring that updates are performed according to the primary key, which can be a bit cumbersome if the where condition is not the primary key.

Do you want to update the primary key? Is there another way to do it? We can only ask the typical update...where for help in this case, here is an example:

1	UPDATEtable1 SET col1 = 1 WHERE table1.col2 = (SELECT col2 FROM table2 WHERE table2.col2 = table1.col2 ANDtable2.col3 = 5);

From for notes (Wiz)

SQLite Data Multi-table federated update