James James - 1 year ago 79
MySQL Question

SQL Query Returning Deleted Rows

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


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:

, sur.ROLE_ID
FROM support_users su
, support_users_roles sur
, support_roles sr
AND su.IS_ACTIVE != ā€˜Nā€™
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download