CODEWITHSUNDEEP
mysql
Niflhel
Niflhel

Reputation: 663

MySQL - Clone a row and all its children

I have a table named users. This table is linked to agendas, which is itself linked to events.

An agenda can have 0 or n event.

So now, what if I want to clone user #3 with all of his agendas and events, keeping all foreign keys up to date?

I know how to do that with multiple queries (SELECT then INSERT, get inserted_id and so on...), but I have no clue how to do that in a single query.

I also found how to copy the user's row, but not its children:

CREATE TEMPORARY TABLE tmp_users SELECT * FROM users WHERE userID = 3;
UPDATE tmp_users SET userID = NULL;
INSERT INTO users SELECT * FROM tmp_users;
DROP TEMPORARY TABLE IF EXISTS tmp_users;

So if anyone got an idea, I 'm not a pro of mySQL and I don't event know if that's possible ... :)

Upvotes: 2

Views: 351

Answers (1)

peterm
peterm

Reputation: 92785

IMHO you're looking for something like this

INSERT INTO users (user_name, ...)
SELECT user_name, ...
  FROM users
 WHERE user_id = 3;

SET @last_user_id = LAST_INSERT_ID();

INSERT INTO agendas (user_id, agenda_name, ...)
SELECT @last_user_id, agenda_name, ...
  FROM agendas
 WHERE user_id = 3;

INSERT INTO events (agenda_id, event_name, ...)
SELECT a3.agenda_id_new, e.event_name, ...
  FROM events e JOIN
(SELECT a1.agenda_id agenda_id_old, 
       a2.agenda_id agenda_id_new
  FROM
(SELECT agenda_id, @n := @n + 1 n 
   FROM agendas, (SELECT @n := 0) n 
  WHERE user_id = 3 ORDER BY agenda_id) a1 JOIN
(SELECT agenda_id, @m := @m + 1 m 
   FROM agendas, (SELECT @m := 0) m 
  WHERE user_id = @last_user_id ORDER BY agenda_id) a2 ON a1.n = a2.m) a3 
     ON e.agenda_id = a3.agenda_id_old;

SQLFiddle

The assumption is that all tables have id columns (user_id, agenda_id, event_id) set to auto_increment

And you can always to wrap it up into a stored procedure with an input parameter of a user being cloned.

Upvotes: 3

Related Questions