INSERT INTO table2 SELECT FROM table1 then UPDATE table1 rows that selected/inserted

Question

I am writing a procedure to copy data from a table data_entry to another table promotional Table structure of data_entry is as below (excluded non-related fields ) -

CREATE TABLE `data_entry` (
  `school_id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) NOT NULL,
  `mobile_number` varchar(15) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `city` varchar(250) DEFAULT NULL,
  `pin` varchar(6) DEFAULT NULL,
  `is_copied_to_promo` tinyint(4) DEFAULT '0'
  PRIMARY KEY (`school_id`)
)

Table structure of promotional (excluded non-related fields )

CREATE TABLE `promotional` (
      `promo_id` int(11) NOT NULL AUTO_INCREMENT, //renamed to avoid confusion
      `school_name` varchar(255) NOT NULL,
      `mobile_number` varchar(15) DEFAULT NULL,
      `email` varchar(50) DEFAULT NULL,
      `website` varchar(255) DEFAULT NULL,
      `city` varchar(250) DEFAULT NULL,
      `pin` varchar(6) DEFAULT NULL,
      `copied_school_id` INT, // edit - school_id of data_entry table will go here
       PRIMARY KEY (`promo_id`)
   )

Here is the procedure to copy all rows from data_entry to promotional where is_copied_to_promo=0.

Procedure

CREATE PROCEDURE `uspCopySchoolsToPromotional`(IN param_insert_datetime DATETIME)
BEGIN
    INSERT IGNORE INTO promotional (
      school_name,
      mobile_number,
      email,
      website,             
      city,
      pin,
      copied_school_id                                   
    )
SELECT school_name,
    mobile_number,
    email,
    website,
    city,
    pin,
    school_id
FROM data_entry
  WHERE is_copied_to_promo =0 ;
END;

What I want to do now to update is_copied_to_promo to 1 in data_entry table for all the inserted/affected rows from above procedure so that each time I execute above procedure only new rows from data_entry table should copy to promotional.

I am calling this procedure via PHP code. The solution can be adding update query in the same procedure or run another query/procedue after executing uspCopySchoolsToPromotional.

Thanks in advance.

EDIT :

I forgot to mention that school_id in both tables are different. In promotional table data is coming from multiple sources. So I renamed the school_id with promo_id in promotional table to avoid your confusion.

Answer 1

If you copied over the school_id as well then you have a unique key to work with, and you could then add an UPDATE after the INSERT to do this, like:

CREATE PROCEDURE `uspCopySchoolsToPromotional`(IN param_insert_datetime DATETIME)
BEGIN
    INSERT IGNORE INTO promotional (
      school_id, 
      school_name,
      mobile_number,
      email,
      website,             
      city,
      pin                                   
    )
SELECT school_id,
    school_name,
    mobile_number,
    email,
    website,
    city,
    pin,
FROM data_entry
  WHERE is_copied_to_promo =0 ;

UPDATE data_entry 
    SET is_copied_to_promo=1 
WHERE 
    school_id=(SELECT school_id FROM promotional) 
    AND is_copies_to_promo=0;
END;

Hope this helps :)