title: How can I merge duplicate rows with foreign key constraints?
body: <p>I've been trying to rack my brain on this for the past four hours, and I'm in a state of confusion, so I'm wondering if you'd be able to help me?</p>
  
  <p>I have two tables. </p>
  
  <p><strong>Staff</strong></p>
  
  <pre><code>CREATE TABLE `staff` (
    `id` int(11) NOT NULL,
    `alias` varchar(255) DEFAULT NULL,
    `first_name` varchar(255) DEFAULT NULL,
    `last_name` varchar(255) DEFAULT NULL,
    `clerk_code` varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
  ALTER TABLE `staff`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `id` (`id`);
  </code></pre>
  
  <p><strong>Transactions</strong></p>
  
  <pre><code>CREATE TABLE `transactions` (
    `id` int(11) NOT NULL,
    `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `till_id` int(11) DEFAULT NULL,
    `raw_journal` mediumtext,
    `journal_id` int(11) NOT NULL DEFAULT '0',
    `staff_id` int(11) DEFAULT NULL,
    `customer_id` int(11) DEFAULT '0',
    `journal_person_count` int(11) DEFAULT '0',
    `journal_table` varchar(255) DEFAULT '''0''',
    `gross_total` varchar(255) DEFAULT '0',
    `journal_tax_total` varchar(255) DEFAULT '0',
    `journal_discount_total` varchar(255) DEFAULT '0',
    `journal_non_add` varchar(255) DEFAULT NULL,
    `journal_training` varchar(255) DEFAULT '0',
    `journal_bill_id` varchar(255) DEFAULT '0',
    `journal_room_id` varchar(255) DEFAULT '0',
    `journal_cancel_sale` varchar(255) DEFAULT '0',
    `journal_receipt_id` varchar(255) DEFAULT '0',
    `job_index` varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
  ALTER TABLE `transactions`
    ADD PRIMARY KEY (`journal_id`),
    ADD UNIQUE KEY `id` (`id`),
    ADD KEY `staff` (`staff_id`);
  
  
  ALTER TABLE `transactions`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  
  
  ALTER TABLE `transactions`
    ADD CONSTRAINT `staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  </code></pre>
  
  <p><strong>What I'm looking to achieve...</strong>
  My staff table contains ~3,000 rows, some of which have multiple duplicates, for arguments sake lets say:</p>
  
  <pre><code>+------+-------+------------+-----------+------------+
  | id   | alias | first_name | last_name | clerk_code |
  +------+-------+------------+-----------+------------+
  |    9 |       | Manager    | a         | 3597       |
  |   10 |       | Manager    | a         | 3597       |
  |   11 |       | Manager    | a         | 3597       |
  |   16 |       | Manager    | b         | 9999       |
  |  436 |       | Manager    | c         | 3126       |
  | 1230 |       | Manager    | c         | 3126       |
  | 1231 |       | Manager    | c         | 3126       |
  | 1232 |       | Manager    | c         | 3126       |
  | 1354 |       | Manager    | a         | 3597       |
  +------+-------+------------+-----------+------------+
  </code></pre>
  
  <p>Now each staff row has multiple transactions (~14,000,000 rows in total), I want to be able merge / delete my duplicate staff rows and update the relative rows in transactions, keeping that association so that staff_id is now the only record in existence for each staff member. </p>
  
  <p>I feel like there must be an easy way to achieve this and I'm over complicating it in my mind, if you wouldn't mind providing me with an example query and talking me through it so I can learn from it I would really appreciate that. Also if you need me to clarify anything please ask.</p>
  
  <p><em>Edit: I'd just like to add that I know the data types for each column are rough, this is only a temporary database as part of a migration.</em></p>