CODEWITHSUNDEEP
mysqlsql
Matthew
Matthew

Reputation: 7725

Copy row and all of its 'children'

I have a MySQL table called employees. There's another table called shifts that is tied to employees by the employeeId column and a one-to-many relationship. I want to clone an employee and his shifts using INSERT - so the employee row is cloned, and the shifts are all cloned and have the new auto-incrementing primary key that was generated for the new employee. Is this possible in one query?

Upvotes: 3

Views: 1217

Answers (2)

Eli
Eli

Reputation: 38949

If you want to do this many times, or want to do it on a bulk level, create an sp that inserts a dupe entry for an employee and then inserts dupe entries for all his shifts.

Something like:

CREATE PROCEDURE sp_cloneEmployee
@employeeId int
AS
INSERT INTO employees
SELECT * FROM employees
WHERE employeeId = @employeeId;

INSERT INTO shifts
SELECT * FROM shifts 
WHERE employeeId = @employeeId;

Upvotes: 2

Tom Studee
Tom Studee

Reputation: 10452

Yes but not with a single insert.

You'd have one INSERT to do the employee, then a second INSERT with a sub-SELECT to SELECT all of the shift data.

Here are some examples

Upvotes: 5

Related Questions