I'm having problem with my transaction. I want to be able to update 3 tables then rollback the changes at the end of the transaction.
SET autocommit = 0;
UPDATE memberinfor SET mPoint = 0, mAction = 9, mSubCardNo1 = '', mSubCardNo2 = '', mUserID = 'ADMIN', dataStatus = 'N', mModifiedOutlet = '004'
WHERE mAccNo = 2880040017090;
UPDATE mcardlist SET STATUS = '10', remark = '10', canRenew = '0'
WHERE mAccNo = '2880040017090' AND STATUS = '2' AND mCardNo = '1234547567345232' OR mcardno = '' AND cDate = '2011-01-24'
UPDATE memstatustrans SET mRemarks = 'Is Void'
WHERE maccNo = '2880040017090' AND mRemarks LIKE '%99000590%' AND cDate = '2011-01-24'
but never mind rolling back, my query did not go pass updating the first table, and to make things more confusing, when my mouse cursor is clicked at any of the update table statement, only that update statement is run.
Aaaa it's so confusing and depressing for a newb in mysql like me
What tool are you using to perform the updates? Could it be that when you click on the update statement it is executing the entire set of commands in the transaction in which case the results are as you would expect them? If it performed the 3 update statements and then rolled back the transaction then you would see no changes in the database when you attempt to go back and review them.