SQL Delete from two table in Oracle

Question

I have to do remove the row (containing the userId) in the table "USERS". This is my query:

@SqlUpdate("delete from USERS where userId = :userId ")
void removeUser(@Bind("userId") String userId);

But first I want to remove that user from the table "USERS_DATA" (that is a daughter of USERS) which also contain the "userId". How can I do? I've tried this:

@SqlUpdate("delete from USERS_DATA where userId = :userId " +
      " and delete from USERS where userId = :userId")
void removeUser(@Bind("userId") String userId);

but console tell me: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

Answer 1

Unlike some other RDBMS, Oracle does not allow you to pass two statements in the same SQL command (this helps to prevent SQL injection).

You can try using wrapping both queries in an anonymous PL/SLQ block:

BEGIN
  delete from USERS_DATA where userId = :userId;
  delete from USERS      where userId = :userId;
END;
/

This will allow you to execute both DML statements together as they are part of the singular containing PL/SQL block.

Unfortunately, I am not familiar with that annotation syntax in Java so I cannot help you convert it to Java but I would guess at:

@SqlUpdate("BEGIN " +
  "delete from USERS_DATA where userId = :userId; " +
  "delete from USERS      where userId = :userId; " +
"END;")
void removeUser(@Bind("userId") String userId);

Alternatively, you can create a procedure in Oracle:

CREATE OR REPLACE PROCEDURE delete_user(
  in_userID  USERS_DATA.USERID%TYPE
)
AS
BEGIN
  DELETE FROM USERS_DATA WHERE userId = in_userId;
  DELETE FROM USERS      WHERE userId = in_userId;
END;
/

And you can then just call this procedure.