CODEWITHSUNDEEP
mysqlcopy
Ray S.
Ray S.

Reputation: 1200

copy section/category while updating linked column

I have two linked tables of section/category structure.

the section table structure
id sec_title
1  section 1
2  section 2

the category structure
id sectionid cat_title
1  1  cat1
2  1  cat2
3  2  cat 3
4  2  cat 4

is there an easy way to copy a section with all of its categories into the same section and categories table?

the problem if i do a straight copy is that the "sectionid" of the copied rows in the category table is not of the id of the new section rows. instead it is the sectionid of the old rows.

I need the "sectionid" to be of the ids of the new inserted section rows.

copied version

section table structure:

id sec_title
1  section 1
2  section 2
3  copy of section 1
4  copy of section 2

the category structure
id sectionid cat_title
1  1  cat1
2  1  cat2
3  2  cat 3
4  2  cat 4
5  3  cat1
6  3  cat2
7  4  cat 3
8  4  cat 4

Upvotes: 0

Views: 35

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED You can clone several sections at once like this 

INSERT INTO section (sec_title) 
SELECT CONCAT('Copy of ', sec_title)
  FROM section
 WHERE id IN(1, 2)
 ORDER BY id;

INSERT INTO category (sectionid, cat_title)
SELECT LAST_INSERT_ID() + rnum, cat_title
  FROM 
(
  SELECT sectionid, cat_title, @n := IF(@g = sectionid, @n + 1, 0) rnum, @g := sectionid
    FROM category CROSS JOIN (SELECT @n := -1, @g := NULL) i
   WHERE sectionid IN (1, 2)
   ORDER BY sectionid, id
)q;

Here is SQLFiddle demo

Answer to original question:

Is there an easy way to copy a section with all of its categories into the same section and categories table?

Assuming that id columns in both tables are auto_increment columns and you want to clone section 2 you can do it like this

INSERT INTO section (sec_title) 
SELECT CONCAT('Copy of ', sec_title)
  FROM section
 WHERE id = 2;
INSERT INTO category (sectionid, cat_title)
SELECT LAST_INSERT_ID(), cat_title
  FROM category
 WHERE sectionid = 2;

Here is SQLFiddle demo

You can wrap it in a stored procedure

DELIMITER $$
CREATE PROCEDURE clone_section(IN _sectionid INT)
BEGIN
  INSERT INTO section (sec_title) 
  SELECT CONCAT('Copy of ', sec_title)
    FROM section
   WHERE id = _sectionid;
  INSERT INTO category (sectionid, cat_title)
  SELECT LAST_INSERT_ID(), cat_title
    FROM category
   WHERE sectionid = _sectionid;
END$$
DELIMITER ;

And then use it

CALL clone_section(2);

Here is SQLFiddle demo

Upvotes: 1

Related Questions