CODEWITHSUNDEEP
mysqlsql-updatesql-insertatomic
Mitchell
Mitchell

Reputation: 343

Run multiple queries where all succeed or all fail

I need to update 2 rows in to 2 different tables then insert a new row in to another different table which must be done so either they all succeed or fail. (I think the word is atomically?). I did some searching but can only seem to find questions related to doing multiple inserts or updates to one table.

Below are the 3 queries I need to run:

UPDATE submission SET status='a' WHERE idgreg = 119 AND status='p' AND userid = 126;

UPDATE greg SET iscomplete=1 WHERE idgreg = 119;

INSERT INTO completion (idperson, idgreg, datecompleted, idsubmission, mediaid, description, 
privatestatus, contenttype, totalamount)  
SELECT s.idperson, s.idgreg, s.datesubmitted, s.idsubmission, COALESCE(s.youtubeid, s.contentid) AS mediaidid, g.description, g.privatestatus, 
g.contenttype, COALESCE(SUM(amount),0) AS totalamount 
FROM submission s 
INNER JOIN greg g on s.idgreg = g.idgreg 
INNER JOIN contribution c on s.idgreg = c.idgreg 
WHERE s.idsubmission = 36 AND c.ispaid = 1;

Upvotes: 1

Views: 844

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521599

You should wrap your queries inside a MySQL transaction to ensure that they execute atomically. But first, declare error handlers which will ROLLBACK all changes in the event of an error:

CREATE PROCEDURE runYourQueries()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;

UPDATE submission SET status='a' WHERE idgreg = 119 AND status='p' AND userid = 126;

UPDATE greg SET iscomplete=1 WHERE idgreg = 119;

INSERT INTO completion (idperson, idgreg, datecompleted, idsubmission, mediaid, description,
    privatestatus, contenttype, totalamount)  
SELECT s.idperson, s.idgreg, s.datesubmitted, s.idsubmission,
    COALESCE(s.youtubeid, s.contentid) AS mediaidid, g.description, g.privatestatus,
    g.contenttype, COALESCE(SUM(amount),0) AS totalamount 
FROM submission s 
INNER JOIN greg g on s.idgreg = g.idgreg 
INNER JOIN contribution c on s.idgreg = c.idgreg 
WHERE s.idsubmission = 36 AND c.ispaid = 1;

COMMIT;

END

Upvotes: 1

Related Questions