Search code examples
mysqlsqldatabasesql-updatemigration

MySQL bulk update a new table based on results of another table

I have a fairly complex database structure with over 1 million records. I am attempting to migrate the data from an existing entity to a new entity.

The structure for the old data table (which I am migrating) looks like this:

MAIN / EXISTING TABLE

|-------|-----------|-----------|
| title | text      | parent_id |
|-------------------|-----------|
| HELLO | Something | 3001      |
|-------|-----------|-----------|
| HELLO | Hi!       | 3002      |
|-------|-----------|-----------|
| TEST  | World!    | 3001      |
|-------|-----------|-----------|

It has over 1 million records.

The new table in which is the destination for the migration has the title attributes from the existing table as columns names.

NEW TABLE

|---|-----------|-------|------|-------|
|id | parent_id | HELLO | TEST | OTHER |
|---|-----------|-------|------|-------|
| x | 3001      | NULL  | NULL | NULL  |
|---|-----------|-------|------|-------|
| y | 3002      | NULL  | NULL | NULL  |
|---|-----------|-------|------|-------|
| z | 3003      | NULL  | NULL | NULL  |
|---|-----------|-------|------|-------|

The desired outcome for the New table based upon the existing data would be the following:

DESIRED NEW TABLE

|---|-----------|-----------|--------|-------|
|id | parent_id | HELLO     | TEST   | OTHER |
|---|-----------|-----------|--------|-------|
| x | 3001      | Something | World! | NULL  |
|---|-----------|-----------|--------|-------|
| y | 3002      | Hi!       | NULL   | NULL  |
|---|-----------|-----------|--------|-------|
| z | 3003      | NULL      | NULL   | NULL  |
|---|-----------|-----------|--------|-------|

The outcome can be achieved programmatically like this (pseudocode):

FOREACH row IN `MAIN TABLE`:
    UPDATE `NEW TABLE` SET `row.title` = 'row.text'

But this is not a viable solution because there are so many records to loop over.

Does anybody know if the solution is possible in MySQL?

Solution

  • You can try below -

    UPDATE 
  NEWTABLE 
  INNER JOIN (
    select 
      max(
        case when title = 'HELLO' then text end
      ) as hello, 
      max(
        case when title = 'TEST' then text end
      ) as test, 
      max(
        case when title not in ('HELLO', 'TEST') then text end
      ) as other 
    from 
      MAINTABLE 
    group by 
      parent_id
  ) A ON NEWTABLE.parent_id = A.parent_id 
SET 
  HELLO = A.hello, 
  TEST = A.test, 
  OTHER = A.other