MYSQL: create an insert with data from two subqueries

Question

I know i can use a subquery to make an insert after selecting rows from the database.

INSERT INTO wp_43_term_relationships (object_id, term_taxonomy_id, term_order)
SELECT ID, 8363, 0 FROM
( SELECT DISTINCT
wp_43_posts.ID
FROM
wp_43_posts
INNER JOIN wp_43_term_relationships ON wp_43_term_relationships.object_id = wp_43_posts.ID
INNER JOIN wp_43_term_taxonomy ON wp_43_term_taxonomy.term_taxonomy_id = wp_43_term_relationships.term_taxonomy_id
WHERE  (wp_43_term_taxonomy.term_id = 4613 OR wp_43_term_taxonomy.term_id = 4615) AND wp_43_term_taxonomy.term_id != 8363 ) posts

But what if, instead of hardcoding 8363, i'd need to derive it from another query?For example I'd need to deriv it from

SELECT
wp_43_term_taxonomy.term_taxonomy_id
FROM
wp_43_term_taxonomy
WHERE
wp_43_term_taxonomy.term_id = 8363

Looks like you have several hardcoded values here ; maybe it's time to consider generating a .sql file containing your INSERT queries from a simple script (whatever being your preferred language for that, PHP, Python etc), allowing to retrieve values into variables before binding them to queries, prepare arrays of identifiers to use in loops... — Maxime Pacary
– Maxime Pacary, Commented Sep 29, 2015 at 21:43
@FrostyZ it would take 2 minutes to make a wp cli command, you are right, but i have to use mysql because sometimes it's easier. And i was curious. I could have simply run the other query and hardcoded the value but wanted to know how to do this! — Nicola Peluchetti
– Nicola Peluchetti, Commented Sep 29, 2015 at 21:48

Lajos Veres · Accepted Answer · 2015-09-29 21:40:16Z

1

If you replace the constant with the second query in ()'s, it should work. Like this:

INSERT INTO wp_43_term_relationships (object_id, term_taxonomy_id,term_order)
SELECT ID,
(SELECT wp_43_term_taxonomy.term_taxonomy_id FROM wp_43_term_taxonomy WHERE wp_43_term_taxonomy.term_id = 8363),
0 FROM
... remaining of the original ...

At least if you need only 1 value, otherwise it would be better to join this extra table to the others.

answered Sep 29, 2015 at 21:40

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2015-09-29 21:38:51Z

1

Yes you can, see modified query below. Essentially include that column into subquery select clause you want to make for INSERT statement. Also, you certainly don't need that condition AND wp_43_term_taxonomy.term_id != 8363

INSERT INTO wp_43_term_relationships (object_id, term_taxonomy_id, term_order)
SELECT ID, term_id, 0 
FROM
(SELECT DISTINCT
wp_43_posts.ID, 
wp_43_term_taxonomy.term_id
FROM wp_43_posts
INNER JOIN wp_43_term_relationships 
ON wp_43_term_relationships.object_id = wp_43_posts.ID
INNER JOIN wp_43_term_taxonomy 
ON wp_43_term_taxonomy.term_taxonomy_id = wp_43_term_relationships.term_taxonomy_id
WHERE wp_43_term_taxonomy.term_id IN(4613, 4615) posts

answered Sep 29, 2015 at 21:38

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

void · Accepted Answer · 2015-09-29 21:42:05Z

1

just bring it into the subquery and use an extra join:

INSERT INTO wp_43_term_relationships (object_id, term_taxonomy_id, term_order)
SELECT ID, someval, 0 FROM
( SELECT DISTINCT
wp_43_posts.ID,t.term_taxonomy_id someval
FROM
wp_43_posts
INNER JOIN wp_43_term_relationships ON wp_43_term_relationships.object_id = wp_43_posts.ID
INNER JOIN wp_43_term_taxonomy ON wp_43_term_taxonomy.term_taxonomy_id = wp_43_term_relationships.term_taxonomy_id
WHERE  (wp_43_term_taxonomy.term_id = 4613 OR wp_43_term_taxonomy.term_id = 4615) AND wp_43_term_taxonomy.term_id != 8363 
join wp_43_term_taxonomy t on t.term_id = 8363 ) posts

edited Sep 29, 2015 at 21:42

answered Sep 29, 2015 at 21:36

void

7,9503 gold badges29 silver badges47 bronze badges

Collectives™ on Stack Overflow

MYSQL: create an insert with data from two subqueries

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related