SQL failed to execute the “ROLLBACK” command
In the process of querying i made a mistake in an UPDATE statement which affected my "UNIT" field (all UNIT changed to 500). I used the ROLLBACK but dit not work. Please help
Software/Hardware used:
SQL in WAMP
Software/Hardware used:
SQL in WAMP
ASKED:
March 5, 2011 10:52 PM
UPDATED:
March 7, 2011 2:27 PM
Answer Wiki:
Before you can issue a rollback you have to explicitly issue the BEGIN TRANSACTION statement, like this.
<pre>BEGIN TRANSACTION
UPDATE YourTable
SET Unit = 500
ROLLBACK</pre>
If you don't include the BEGIN TRANSACTION statement you can't issue the ROLLBACK statement.
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you for the answer,
may be i did not do the execution well. I had the following error
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘TRANSACTION UPDATE invoice SET Unit = 500 ROLLBACK’ at line 1
when I typed the statements below
BEGIN TRANSACTION
UPDATE invoice
SET Unit = 500
ROLLBACK;
the table did not “rollback”
NB:
Table name: INVOICE
fields :Item, Qty, Unit
i am looking forward to hearing from you
Okappaw, Mrdenny’s answer applies to SQL Server only but it seems that you are using MySql. That is why it is so important to clearly specify the products you are using.
On MySQL, each command must be ended with a semicolon, and OTOH “BEGIN TRANSACTION” is not a valid command on MySQL.
Additionally, autocommit may be enabled in your database. If so, you can’t rollback your transaction.