SQLite 是否支持“删除自"? [英] Does SQLite support "delete from from"
本文介绍了SQLite 是否支持“删除自"?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这在 Microsoft SQL Server 的 T-SQL 上是有效的语法,但在 SQLite 中无效,是否有替代语法在 SQLite 中执行相同的操作?
This is valid syntax on Microsoft SQL Server's T-SQL, but not in SQLite, is there an alternative syntax that does the same in SQLite?
DELETE FROM something
FROM something
INNER JOIN combinations ON something.field1=combinations.field1
AND something.field2=combinations.field2
--optionally more criteria and/or more joins
WHERE combinations.field3=1234
--or anything really, just to show some criteria could be applied here also
推荐答案
一般情况下,将整个 join 移动到查找要删除行的主键的子查询中:
In the general case, move the entire join into a subquery that looks up the primary keys of the rows to be deleted:
DELETE FROM something
WHERE id IN (SELECT something.id
FROM something
JOIN combinations ON something.field1=combinations.field1
AND something.field2=combinations.field2
WHERE combinations.something=1234)
如果您有复合主键,则可以使用 rowid 代替:
If you have a composite primary key, you can use the rowid instead:
DELETE FROM something
WHERE rowid IN (SELECT something.rowid
FROM something
JOIN combinations ON something.field1=combinations.field1
AND something.field2=combinations.field2
WHERE combinations.something=1234)
如果您有一个复合主键,并且您将表创建为 WITHOUT ROWID 表,您必须将连接重写为相关子查询:
If you have a composite primary key and you created the table as a WITHOUT ROWID table, you have to rewrite the join as a correlated subquery:
DELETE FROM something
WHERE EXISTS (SELECT 1
FROM combinations
WHERE field1 = something.field1
AND field2 = something.field2
AND field3 = 1234)
这篇关于SQLite 是否支持“删除自"?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文