CODEWITHSUNDEEP
mysqlsql
Nicola Peluchetti
Nicola Peluchetti

Reputation: 76880

MYSQL: create an insert with data from two subqueries

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

Upvotes: 0

Views: 50

Answers (3)

void
void

Reputation: 7890

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

Upvotes: 1

Lajos Veres
Lajos Veres

Reputation: 13725

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.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

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

Upvotes: 1

Related Questions