Delete based on a join?

[Foxsterc]

Hi,

Is is possible to delete based on joining X tables with criteria from both?

Thanks

[simolus3]

It's not possible to have joins on delete statements, but in most cases it's possible to come up with a subquery to use in WHERE that does the same thing. E.g. if you wanted to do DELETE FROM t1 JOIN t2 ON t2.foo = t1.bar WHERE t2.another = 3;, you could instead do

final deleteStmt = delete(t1);
deleteStmt.where((t1) {
  final subquery = db.select(t2)
    ..where((t2) => t2.foo.equalsExp(t1.bar) & t2.another.equals(3));
  return existsQuery(subquery);
});

await deleteStmt.go();

[Foxsterc]

That's perfect! I hadn't thought of using a subquery in drift.

Thanks,