Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am attempting to clone a row in the database, not that difficult. However the row has a reference to another row in the database that when inserted needs to use a different value, perhaps provided by a previous insert.

For example this is the table structure.

CREATE TABLE `usergroup` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `vhost` int(11) unsigned NOT NULL,
    `groupType` tinyint(3) unsigned NOT NULL DEFAULT '1',
    `name` varchar(80) NOT NULL,
    `description` text,
    `note` text,
    `logo` int(11) unsigned NOT NULL DEFAULT '0',
    `created` datetime DEFAULT NULL,
    `treeleft` int(10) NOT NULL,
    `treeright` int(10) NOT NULL,
    `parentGroup` int(11) unsigned NOT NULL DEFAULT '0',
    `geo_radius` int(11) unsigned NOT NULL DEFAULT '0',
    `geo_autoRemove` tinyint(4) NOT NULL DEFAULT '1',
    `geo_autoAdd` tinyint(4) NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    KEY `vhost` (`vhost`,`groupType`,`moderationStatus`,`parentGroup`,`created`),
    KEY `tree` (`vhost`,`treeleft`,`treeright`)
) ENGINE=InnoDB

With the following rows

INSERT INTO `usergroup` (id, vhost, groupType, name, description, note, created, treeleft, 
                         treeright, parentGroup, geo_radius, geo_autoRemove, geo_autoAdd) 
      VALUES (9696, 222, 1, 'Group 1.1', NULL, NULL, NULL, 1, 12, 0, 0, 0, 0),
             (9697, 222, 1, 'Group 1.2', '', '', NULL, 2, 3, 9696, 0, 0, 0),
             (9698, 222, 1, 'Group 1.3', '', '', NULL, 4, 9, 9696, 0, 0, 0),
             (9700, 222, 1, 'Group 1.4', NULL, NULL, NULL, 5, 6, 9698, 0, 0, 0),
             (9701, 222, 1, 'Group 1.5', NULL, NULL, NULL, 7, 8, 9698, 0, 0, 0),
             (9702, 222, 1, 'Group 1.6', NULL, NULL, NULL, 10, 11, 9696, 0, 0, 0),
             (9706, 222, 1, 'Group 1.9', NULL, NULL, NULL, 13, 16, 0, 0, 0, 0),
             (9707, 222, 1, 'Group 1.10', NULL, NULL, NULL, 14, 15, 9706, 0, 0, 0),
             (9704, 222, 1, 'Group 1.7', NULL, NULL, NULL, 17, 18, 0, 0, 0, 0),
             (9705, 222, 1, 'Group 1.8', NULL, NULL, NULL, 19, 20, 0, 0, 0, 0),
             (9799, 222, 1, 'Group 1.11', '', '', '2012-06-21 17:11:41', 21, 22, 0, 0, 0, 0),
             (9800, 222, 1, 'Group 1.12', '', '', '2012-06-24 10:21:34', 23, 24, 0, 0, 0, 0);

I now want to clone all these usergroups into a new vhost, 333 for example.

    INSERT INTO usergroup (vhost, groupType, name, description, note, 
        created,  treeleft, treeright, parentGroup, geo_radius, 
        geo_autoRemove, geo_autoAdd) VALUES 
            (SELECT 333 as vhost, groupType, name, description, 
                    note, created, treeleft, treeright, parentGroup, 
                    geo_radius, geo_autoRemove, geo_autoAdd 
               FROM usergroup 
              WHERE vhost = 222);

The problem of course is while everything works out perfectly the parentGroup is incorrect. I suppose inserting by joining the table with itself might work but I need to be able to clone a vhost that has 8 levels of depth but could be as few as two in some places.

Does anyone have any ideas?

EDIT

I ended up doing this via a php script I wrote but if anyone has any ideas on how to do this via SQL that would be interesting.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.