James James - 5 months ago 16
MySQL Question

SQL Query Returning Deleted Rows

I currently have the following table in my database, with the stated data in it:

SUPPORT_USER_ROLES TABLE:

USER_ID | ROLE_ID
1 | 1
1 | 2
2 | 2
*2* | *1*


The bottom row, notated by '*' has been deleted and in SQL Developer there is no sign of it.

I am now running the following query:

SELECT su.USER_ID
, su.USERNAME
, sur.ROLE_ID
, sr.ROLE_NAME
FROM support_users su
, support_users_roles sur
, support_roles sr
WHERE su.USER_ID = sur.USER_ID
AND su.IS_ACTIVE != ā€˜Nā€™
AND sr.ROLE_ID = sur.ROLE_ID
AND sr.IS_ACTIVE != ā€˜Nā€™


and it is returning the following:

{ (USER_ID : 1, ROLE_ID : 1),
(USER_ID : 1, ROLE_ID : 2),
(USER_ID : 2, ROLE_ID : 1),
(USER_ID : 2, ROLE_ID : 2) }


as you can see, it is still giving me:

(USER_ID : 2, ROLE_ID : 1)


even though it doesn't exist anywhere anymore.

I am using Java, JSF, PrimeFaces, SQL Developer, Hibernate, Weblogic and Eclipse IDE. I'm not sure if I'm massively missing something here and need to refresh/update something somewhere.

I haven't had this before so I'm massively confused at the moment :S

Any help is greatly appreciated!

Answer

Putting comment as answer after knowing the issue

Please make sure to use commit from the same connection as your program from where you ran the delete statement. Deleting from application and commiting on SQL Developer will not work.

Right now you can do one of two things

(1) Change your code and just run commit once and then make sure to use commit after every DML statement.

(2) Use the delete and commit together. If the data is already deleted, it wont be deleted again and commit will happen after that. If it is not deleted, then if would be deleted and committed.