Deleting several rows in table of DB

Question

I am using code-generator. I have two tables. In one table I have user_id an several docs of this user_id. In second table a have docs of this user_id, but without user_id and I have to delete these docs. Please help!

Answer 1

I'm assuming you have some criteria about the user whose documents you want to delete, just not the foreign key? Just use a semi-join, which you can use in DELETE statements as well.

Assuming your search criteria is something like the username:

In SQL

DELETE FROM docs
WHERE docs.user_id IN (
  SELECT user_id
  FROM user
  WHERE username = ?
)

In Java

// Assuming the usual static import:
import static org.jooq.impl.DSL.*;

// Then write:
ctx.deleteFrom(DOCS)
   .where(DOCS.USER_ID.in(
       select(USER.USER_ID)
       .from(USER)
       .where(USER.USERNAME.eq(username))
   ))
   .execute();