Duplicate row in database with relational tables

Question

I am trying to duplicate a page in the database and all related rows.

The problem I am having is because the page_group_id is an identifier for both tables. Is there any way of doing this without looping each of the new "page_groups" records?

pages (page_id, page_name, etc)
page_groups (page_group_id, page_id, etc)
page_group_items (page_group_id, item_id, etc)

UPDATE:

--
-- Table structure for table `pages`
--

CREATE TABLE IF NOT EXISTS `pages` (
  `page_id` int(11) NOT NULL AUTO_INCREMENT,
  `page_name` varchar(255) NOT NULL,
  PRIMARY KEY (`page_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `pages`
--

INSERT INTO `pages` (`page_id`, `page_name`) VALUES
(1, 'My Page'),
(2, 'My other page');

-- --------------------------------------------------------

--
-- Table structure for table `page_groups`
--

CREATE TABLE IF NOT EXISTS `page_groups` (
  `page_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `page_group_name` varchar(255) NOT NULL,
  `page_id` int(11) NOT NULL,
  PRIMARY KEY (`page_group_id`),
  KEY `page_id` (`page_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `page_groups`
--

INSERT INTO `page_groups` (`page_group_id`, `page_group_name`, `page_id`) VALUES
(1, 'My Group', 1),
(2, 'My Group', 2);

-- --------------------------------------------------------

--
-- Table structure for table `page_group_items`
--

CREATE TABLE IF NOT EXISTS `page_group_items` (
  `page_group_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  KEY `item_id` (`item_id`),
  KEY `page_group_id` (`page_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `page_group_items`
--

INSERT INTO `page_group_items` (`page_group_id`, `item_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 2);

The desired result would be when duplicating a page e.g page_id=1 everything in the other 2 tables which reference this page will also be duplicated and updated to the new page_id — John Magnolia, Apr 06 '13 at 14:27
Is this what you want for Aliasing? [http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation][1] [1]: http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation — NosiaD, Apr 06 '13 at 14:30

Joachim Isaksson · Accepted Answer · 2013-04-06T14:49:02.840

Since you're replacing the only unique identifier in each table (the primary key) when copying, I can't see a way of doing it without adding temporary anchor columns to the tables, do the copy and remove them again. Something like this;

ALTER TABLE pages ADD originalpageid INT;
UPDATE pages set originalpageid=page_id;
ALTER TABLE page_groups ADD originalpagegroupid INT;
UPDATE page_groups SET originalpagegroupid=page_group_id;

INSERT INTO pages (page_name,originalpageid)  
  SELECT page_name,originalpageid FROM pages;

INSERT INTO page_groups (page_group_name,page_id,originalpagegroupid) 
  SELECT page_group_name,MAX(pages.page_id),originalpagegroupid
  FROM page_groups
  JOIN pages 
    ON page_groups.page_id=originalpageid
  GROUP BY originalpageid,page_group_name,originalpagegroupid;

INSERT INTO page_group_items(page_group_id,item_id)
  SELECT MAX(page_groups.page_group_id),item_id
  FROM page_group_items
  JOIN page_groups
    ON page_group_items.page_group_id=originalpagegroupid
  GROUP BY originalpagegroupid,item_id;

ALTER TABLE pages DROP COLUMN originalpageid;
ALTER TABLE page_groups DROP COLUMN originalpagegroupid;

An SQLfiddle to test with

If the use case is doing it all the time in the system, it may not be the solution you're looking for, but for manual intervention it should work well.

As always, always back your database up before running SQL from random strangers on the Internet :)

Duplicate row in database with relational tables

1 Answers1