尝试交换SQLite同表两行数据时出现语法错误，如何解决？

问题场景 #

创建的section表结构如下：

db.execSQL("CREATE TABLE IF NOT EXISTS section (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT (255) NOT NULL, image TEXT (20) NOT NULL);");

尝试交换表中两行数据时，执行了以下代码：

SQLiteDatabase db = this.getWritableDatabase();
db.execSQL( "UPDATE section as s1 inner join section as s2 on (s1.id, s2.id) in (("+fromPosition+","+toPosition+"),("+toPosition+","+fromPosition+")) set s1.title=s2.title, s1.image=s2.image");

出现错误：

SQLiteException: near "as": syntax error (Sqlite code 1 SQLITE_ERROR): , while compiling: UPDATE section as s1 inner join section as s2 on (s1.id, s2.id) in ((6,7),(7,6)) set s1.title=s2.title, s1.image=s2.image, (OS error - 0:Success)

错误原因 #

你使用的是MySQL风格的UPDATE ... JOIN语法，但SQLite不支持这种写法，它有自己的UPDATE JOIN规范，不允许在UPDATE后直接跟表别名加JOIN的格式。

解决方案 #

方案一：使用SQLite原生支持的UPDATE FROM语法（推荐） #

这种写法简洁高效，同时用参数化查询避免SQL注入风险：

SQLiteDatabase db = this.getWritableDatabase();
String sql = "UPDATE section " +
             "SET title = s2.title, image = s2.image " +
             "FROM section AS s2 " +
             "WHERE (section.id, s2.id) IN ((?, ?), (?, ?))";
db.execSQL(sql, new Object[]{fromPosition, toPosition, toPosition, fromPosition});

方案二：事务+临时表交换（直观易懂） #

通过临时表暂存两行数据，再分别更新回去，用事务保证操作的原子性：

SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
try {
    // 创建临时表存储目标行数据
    db.execSQL("CREATE TEMP TABLE temp_row (id INTEGER, title TEXT, image TEXT)");
    db.execSQL("INSERT INTO temp_row SELECT id, title, image FROM section WHERE id IN (?, ?)", 
               new Object[]{fromPosition, toPosition});
    
    // 更新fromPosition行的内容为toPosition的原始数据
    db.execSQL("UPDATE section SET title = (SELECT title FROM temp_row WHERE id = ?), " +
               "image = (SELECT image FROM temp_row WHERE id = ?) WHERE id = ?", 
               new Object[]{toPosition, toPosition, fromPosition});
    
    // 更新toPosition行的内容为fromPosition的原始数据
    db.execSQL("UPDATE section SET title = (SELECT title FROM temp_row WHERE id = ?), " +
               "image = (SELECT image FROM temp_row WHERE id = ?) WHERE id = ?", 
               new Object[]{fromPosition, fromPosition, toPosition});
    
    // 删除临时表
    db.execSQL("DROP TABLE temp_row");
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

内容的提问来源于stack exchange，提问作者vickey