Finding matching pairs between two tables and returning a third value to be inserted

Question

I'm working in a database with MySQL thought phpMyAdmin, and it's important to know that my knowledge on MySQL is pretty limited.

I have two tables in my database. I need to take the img_id column values in the second table (wp_posts_thumbs), find the matching pair in the first table (wp_posts), take the corresponding value in the ID column in the first table, and insert it in the post_parent column in the second table.

This is what the first table (wp_posts) looks like:

+------+-------------------------+
| ID   | img_id                  |
+------+-------------------------+
| 1    | W048zewxemq1tw0810aiec  |
| 2    | W0481l2lv4npdczok5mmucl |
| 3    | W0481j9w7fg80b8gkiida85 |
+------+-------------------------+

This is what the second table (wp_posts_thumbs) looks like:

+------+-------------------------+-------------+
| ID   | img_id                  | post_parent |
+------+-------------------------+-------------+
| 101  | W048zewxemq1tw0810aiec  | 0           |
| 102  | W0481l2lv4npdczok5mmucl | 0           |
| 103  | W0481j9w7fg80b8gkiida85 | 0           |
+------+-------------------------+-------------+

Thanks in advance, guys :)

There's no third table, mate :/ `post_parent` is a third column located in the `wp_posts_thumbs` table, just like I illustrated it in my message's content. — Rodrigo D'Agostino, Oct 25 '15 at 01:33

Mike Nakis · Accepted Answer · 2015-10-25T01:43:23.050

1

CREATE TABLE `wp_posts` (
  `Id` INT(11) PRIMARY KEY,
  `img_id` VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE `wp_posts_thumbs` (
  `Id` INT(11) PRIMARY KEY,
  `img_id` VARCHAR(100) UNIQUE NOT NULL,
  `post_parent` INT(11)
);

INSERT INTO wp_posts (id,img_id) VALUES (1,'aiec');
INSERT INTO wp_posts (id,img_id) VALUES (2,'mucl');
INSERT INTO wp_posts (id,img_id) VALUES (3,'da85');
INSERT INTO wp_posts_thumbs (id,img_id,post_parent) VALUES (101,'aiec',0);
INSERT INTO wp_posts_thumbs (id,img_id,post_parent) VALUES (102,'mucl',0);
INSERT INTO wp_posts_thumbs (id,img_id,post_parent) VALUES (103,'da85',0);

UPDATE wp_posts_thumbs 
    LEFT JOIN wp_posts 
        ON wp_posts_thumbs.img_id = wp_posts.img_id 
    SET post_parent = wp_posts.id 
    WHERE wp_posts_thumbs.img_id = wp_posts.img_id;

Yields the following:

101 aiec    1
102 mucl    2
103 da85    3

edited Oct 25 '15 at 01:43

answered Oct 25 '15 at 01:38

Mike Nakis

56,297
11
110
142

I'm trying to test your suggestion, but I'm getting the following error message: `#1052 - Column 'post_parent' in field list is ambiguous` – Rodrigo D'Agostino Oct 26 '15 at 03:39
That's weird, I did not get such a message. Anyhow, try `wp_posts_thumbs.post_parent`. – Mike Nakis Oct 26 '15 at 11:29
Yes, I managed to do that on my own right after I posted my previous comment :P But I've been waiting for 9 hours already and MySQL hasn't finished processing that query. I might need to add new indexes or something. It's taking too long I don't know how to fix it. I only have 4500 rows, it shouldn't be taking that long. – Rodrigo D'Agostino Oct 26 '15 at 13:46
That's also very weird. With 4500 rows it should be done fairly quickly, even if you have not specified any indexes. But yes, try adding indexes for `img_id` on both tables, and also make sure that `post_parent` is *not* indexed while updating it. (If it needs to be indexed, then drop the index prior to updating, and add it again once updating is complete.) – Mike Nakis Oct 26 '15 at 13:51
1

OMG! Now it worked perfectly and in less than a minute! The breaking point was at indexing `img_id` column. That little thing speeded up the whole process. I wouldn't have never guessed how important indexes are. Thank you, thank you so much for your help, @Mike Nakis! I've learn a big lesson thanks to you (and solved a little problem too :P). – Rodrigo D'Agostino Oct 26 '15 at 16:39

score 0 · Answer 2 · edited May 23 '17 at 10:27

This is requires UPDATE with JOIN. You may want to refer to this previous question.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE wp_posts
    (`ID` int, `img_id` varchar(23))
;

INSERT INTO wp_posts
    (`ID`, `img_id`)
VALUES
    (1, 'W048zewxemq1tw0810aiec'),
    (2, 'W0481l2lv4npdczok5mmucl'),
    (3, 'W0481j9w7fg80b8gkiida85')
;


CREATE TABLE wp_posts_thumbs
    (`ID` int, `img_id` varchar(23), `post_parent` int)
;

INSERT INTO wp_posts_thumbs
    (`ID`, `img_id`, `post_parent`)
VALUES
    (101, 'W048zewxemq1tw0810aiec', 0),
    (102, 'W0481l2lv4npdczok5mmucl', 0),
    (103, 'W0481j9w7fg80b8gkiida85', 0)
;

Query 1:

UPDATE wp_posts_thumbs  wpt
   JOIN wp_posts wp ON wpt.img_id = wp.img_id  
   SET wpt.post_parent = wp.id
;

SELECT * FROM wp_posts_thumbs

Results:

|  ID |                  img_id | post_parent |
|-----|-------------------------|-------------|
| 101 |  W048zewxemq1tw0810aiec |           1 |
| 102 | W0481l2lv4npdczok5mmucl |           2 |
| 103 | W0481j9w7fg80b8gkiida85 |           3 |

Finding matching pairs between two tables and returning a third value to be inserted

2 Answers2