same table delete and insert

[shaddf]

is it possible to insert and delete from the same table using the same transaction in mysql.

something like:

 

 START TRANSACTION;

    DELETE FROM Al where vc='frdehy'  ;        

     INSERT INTO Al(vc,zzz ,subj_id) VALUES('bgo' ,'mij','rey');

commit;

[dalecosp]

Yes.

 

Here's one way; $Q is an array of queries.

 

function transaction ($con, $Q){

mysqli_query( $con, "START TRANSACTION" );

for ( $i = 0; $i < count( $Q ); $i++ ) {
if ( !mysqli_query ( $con, $Q[$i] )) {
echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>';
break;
}
}

if ( $i == count ( $Q )) {
mysqli_query( $con, "COMMIT" );
return 1;
} else {
mysqli_query( $con, "ROLLBACK" );
return 0;
}
}

Edited December 3, 2015 by dalecosp

[gizmola]

Yes you can enclose whatever you want in a transaction.  That is the point of them -- and they are of course not limited to one table.  You could insert in table A, Delete from Table B, and update table C, all inside one transaction.

 

The important thing to know is that with mysql your tables need to be of an engine type that supports transactions.  Most people have traditionally used Innodb engine tables for this requirement.

[shaddf]

Yes.

 

Here's one way; $Q is an array of queries.

 

function transaction ($con, $Q){

mysqli_query( $con, "START TRANSACTION" );

for ( $i = 0; $i < count( $Q ); $i++ ) {
if ( !mysqli_query ( $con, $Q[$i] )) {
echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>';
break;
}
}

if ( $i == count ( $Q )) {
mysqli_query( $con, "COMMIT" );
return 1;
} else {
mysqli_query( $con, "ROLLBACK" );
return 0;
}
}

both answers are good