CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 437

SQL - Copy columns to new table

I have a table:

Table Post

+----+--------+-----------------------+
| id | postId |         path          |
+----+--------+-----------------------+
|  1 |     22 | Hello/World           |
|  2 |     23 | Hello/World/test      |
|  3 |     24 | Hello/World           |
|  4 |     25 | World/Monk            |
|  5 |     26 | Test/Test2            |
|  6 |     27 | Hello/World/test      |
|  7 |     28 | Hello/World/Something |
|  8 |     29 | Hello/World/Noa       |
|  9 |     30 | Hello/World           |
+----+--------+-----------------------+

The id is the primary key. postId is also unique. path is not unique. I want a relationship where there is a many-to-one relationship with path and postId. So one postId can have one path, but one path can have many postId's.

Therefor I want to create a new table (Table PostPathMap) that looks like this:

+----+--------+
| id | postId |
+----+--------+
| 22 |      1 |
| 23 |      2 |
| 24 |      3 |
| 25 |      4 |
| 26 |      5 |
| 27 |      6 |
| 28 |      7 |
| 29 |      8 |
| 30 |      9 |
+----+--------+

where the postId is the primary key. Then I want to delete duplicate entries so my Table Post ends up like :

+----+-----------------------+
| id |         path          |
+----+-----------------------+
|  1 | Hello/World           |
|  2 | Hello/World/test      |
|  4 | World/Monk            |
|  5 | Test/Test2            |
|  7 | Hello/World/Something |
|  8 | Hello/World/Noa       |
+----+-----------------------+

I therefore need to update PostPathMap. How can I update this table without doing it manually?

Expected PostPathmap after update:

+----+--------+
| id | postId |
+----+--------+
| 22 |      1 |
| 23 |      2 |
| 24 |      1 |
| 25 |      4 |
| 26 |      5 |
| 27 |      2 |
| 28 |      7 |
| 29 |      8 |
| 30 |      1 |
+----+--------+

Upvotes: 0

Views: 167

Answers (1)

forpas
forpas

Reputation: 164089

Create the table PostPathMap like this:

CREATE TABLE PostPathMap AS
SELECT postId id, id postId FROM Post;

Update PostPathMap:

UPDATE PostPathMap ppm
INNER JOIN (
  SELECT path, MIN(id) id
  FROM Post
  GROUP BY path
) t ON t.id < ppm.postid
INNER JOIN Post p 
ON p.path = t.path AND p.id = ppm.postid
SET ppm.postid = t.id;

and then delete the duplicates from Post with a self join in the UPDATE statement:

DELETE p1 
FROM Post p1 INNER JOIN Post p2
ON p2.path = p1.path AND p2.id < p1.id;

See the demo.

Upvotes: 1

Related Questions