CODEWITHSUNDEEP
mysqldatabase
Thumpers
Thumpers

Reputation: 121

Copy a table and replace foreign surrogate key column with text column

I am building a database / application in MySQL. I am trying to create a Stored Procedure that returns a table of all children that are currently linked to a parent, for display.

The children table is going to be populated with up to 100,000 records.

I want the returned table to be a copy of the child table, except the foreign key column linking the children to the parent (current and previous) should be replaced by a text column containing the parents name, (I don't want to return a surrogate key for display)

These are my two tables

Parent

PARENTID  |  PARENTNAME
-------------------------
1         |  NAME1
2         |  NAMETWO
3         |  ANOTHERNAME

Child

CHILDNAME |  CURRENTPARENTID | PREVIOUSPARENTID  | OTHERDATA COLUMNS... 
-----------------------------------------------------------------------
123ABC    |  2               | 3                 | ..
124ABC    |  2               | 1                 | ..
125ABC    |  1               | 2                 | ..

And when I call the stored procedure to return all children with currentparentID = 2, for instance, I would like the table returned to be

CHILDNAME |  CURRENTPAR_NAME| PREVIOUSPAR_NAME   | OTHERDATA COLUMNS... 
-----------------------------------------------------------------------
123ABC    |  NAMETWO         | ANOTHERNAME       | ..
224ABC    |  NAMETWO         | NAME1             | ..

I can't figure how the INSERT INTO statement would be made

Would it be easier / more efficent to just return the raw children table filtered to currentparentid = 2, and do the assignment on the application side?

Cheers

Upvotes: 0

Views: 52

Answers (1)

Glenn
Glenn

Reputation: 9150

How about an insert statement like this:

INSERT INTO NewTable(CHILDNAME, CURRENTPAR_NAME, PREVIOUSPAR_NAME)
  SELECT c.CHILDNAME, p1.PARENTNAME, p2.PARENTNAME
    FROM Child c
    JOIN Parent p1 ON (p1.PARENTID = c.CURRENTPARENTID)
    JOIN Parent p2 ON (p2.PARENTID = c.PREVIOUSPARENTID)
;

Depending on the structure of the child table, whether it is active, etc, you could tack on a WHERE clause to do the insert in chunks.

Upvotes: 1

Related Questions