users may only delete from a source table, not a joined table.

val basicDelete = deleteFrom(users).where(fName === "Fred") //valid
val joinDelete = deleteFrom((orders join users).on(fkUserId === userId)).where(fName === "Fred") //not valid but compiles

All major dialects provide some way to delete from a joined table but the table to be deleted from needs to be explicitly provided. We could specify a "zio-sql" way of doing this (with dialect specific implementations) e.g. I'd suggest

delete(orders).from((orders join users).on(fkUserId === userId)).where(fName === "Fred")

or we could just disallow this and let the dialect specific modules introduce the concept of deleting from a join in a manner similar to the dialect of choice (e.g. using for postgres)

@jdegoes do you have any preference here?