Wrapping up the repetitive sql statements into transaction in MySQL

Question

I have a task to cancel more than 90 contracts, and I would like to know if it's possible to do it in one shot, rather than running manually all of them. I have 3 statements which I need to run per each contract. These are the examples below.

When I run the first statement:

UPDATE `dbo2`.`allcontracts` SET `ctr_status`='4' WHERE `ctr_id`='4483';

I'm changing the status of contract with id = 4483. Status will be 4 for all contracts ( more than 90 of them ).

When I run the second statement:

INSERT INTO `dbo2`.`tctrchanges`
      (`chg_ctr_id`, `chg_type`, `chg_date`, `chg_status`,
       `chg_appr_date`, `chg_emp_id`, `chg_req`, `chg_reserved`)
    VALUES
      ('4483', '0', '2017-06-02 11:11:11', '1',
       '2017-06-02 11:11:11', '3', '31', '4483');

I need to insert contract id from the first statement into chg_ctr_id and chg_reserved. I will also need id of column chg_id which is PK AI from this second statement above, to automatically assign it to third statement below in column note_text as URL parameter. ( For example below chg_id=5334 )

 INSERT INTO `dbo2`.`allnotes`
       (`note_dt`, `note_user`, `note_ref_id`, `note_type`,
        `note_text`,
        `note_emp_id`, `note_reserved`)
     VALUES
       ('2017-06-02 11:11:11', 'mikeb', '328515', '2',
        '<b>Automatic Note:</b> <a href=\"ctrchange4.asp?chg_id=5334\">Contract was Cancelled</a> ',
        '31', '4483');

As you can notice, the column note_reserved will have the same contract id from the first statement, and column note_emp_id will have the same static value as columnchg_req from the second statement.

All other values can be the same for all contracts.

Any ideas?

Answer 1

I would do something like the following:

CREATE TABLE `DelContracts_20170607` (`contract_id` varchar(32), `chg_id` varchar(32));
INSERT INTO `DelContracts_20160607` VALUES ('1234', NULL);
-- ... insert rest of contract IDs

SET @DelDate = DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d %T');

UPDATE `dbo2`.`allcontracts` ac JOIN `DelContracts_20170607` d ON (ac.`ctr_id` = d.`contract_id`)
   SET ac.`ctr_status`='4'
;

INSERT INTO `dbo2`.`tctrchanges`
  (`chg_ctr_id`, `chg_type`, `chg_date`, `chg_status`, `chg_appr_date`,
   `chg_emp_id`, `chg_req`, `chg_reserved`)
  SELECT `contract_id`, '0', @DelDate, '1', @DelDate, '3', '31', `contract_id`
    FROM `DelContracts_20170607`
;

UPDATE `DelContracts_20170607` d JOIN `dbo2`.`tctrchanges` tcc ON (d.`contract_id` = tcc`chg_reserved`)
   SET d.`chg_id` = CAST(tcc.`chg_id` as char(32))
 WHERE tcc.`chg_date` = @DelDate
;

INSERT INTO `dbo2`.`allnotes`
  (`note_dt`, `note_user`, `note_ref_id`, `note_type`, `note_text`, `note_emp_id`, `note_reserved`)
  SELECT @DelDate, 'mikeb', '328515', '2',
         CONCAT('<b>Automatic Note:</b> <a href=\"ctrchange4.asp?chg_id=',`chg_id`,'">Contract was Cancelled</a> '),
         '31', `contract_id`
    FROM `DelContracts_20170607`
;

DROP TABLE `DelContracts_20170607`;

NOTE: Code is untested.

First, you create a table to hold the contract ID values. Set this up to also hold the chg_id values form tctrchanges, once they're created.

Insert all the contract IDs into the table.

Next set a variable to the current date and time.

Update allcontracts by JOINing it to your newly created table.

Insert your rows into tctrchanges by selecting contract_id, the date variable, and the hardcoded values from your table.

Next, update your table, setting the chg_id value for each contract_id. You find the chg_id based on each row's contract_id and the value from your date variable. This is why we put the date in the variable instead of directly calling CURRENT_TIMESTAMP(); so we'd be sure we had the matching date when we went to search.

Then, insert into allnotes by selecting the hard-coded values (concatenating the chg_id into the URL) and the contract_id from your table yet again.

Finally, delete the table you created.