SQL Delete from two table in Oracle

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

Answers


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.


Need Your Help

Itext PDFReader reading 256-bit AES encrypted pdf

java aes itext

I am receiving an error when trying to open a pdf file to read its contents and map data back into the pdf. The error is:

How to get a well formed opus file with libopus?

c++ file audio encoding opus

I have .wav files and I would to encode them using opus, write everything in an .opus file then read it with vlc for example. I have done some code using the opus trivial example but the quality is...