error : Subquery returns more than 1 row when trying to insert variables into multiple rows from 2 tables MYSQL

Question

The following code matches results from 2 tables and returns multiple results from. So this part of the code works: `

SET @t1 := 
(SELECT
  user_id
FROM
    test.wp_usermeta wp_usermeta,
    test.subs subs
WHERE
    wp_usermeta.meta_value = subs.email
);
SET @t2 := 
(SELECT 
  cusid
FROM
    test.wp_usermeta wp_usermeta,
    test.subs subs
WHERE
    wp_usermeta.meta_value = subs.email
);`

However, when I try to execute the input function with it all, it only adds one row when I actually want it to add a new row for all results the above code gives me. Here is the code with the insert function:

SET @t1 := 
(SELECT
  user_id
FROM
    test.wp_usermeta wp_usermeta,
    test.subs subs
WHERE
    wp_usermeta.meta_value = subs.email
);
SET @t2 := 
(SELECT 
  cusid
FROM
    test.wp_usermeta wp_usermeta,
    test.subs subs
WHERE
    wp_usermeta.meta_value = subs.email
);
INSERT test.wp_usermeta(umeta_id, user_id, meta_key, meta_value)
    VALUES (NULL, @t1, "_stripe_customer_id", @t2)

I get the following error messages:

Query: SET @t1 error : Subquery returns more than 1 row
Query: SET @t2 error : Subquery returns more than 1 row

and this is the only success message:

INSERT test.wp_usermeta(umeta_id, user_id, meta_key, meta_value)
    VALUES (NULL, @t1, "_stripe_customer_id", @t2) : affected rows : 1

Any advice would be greatly appreciated, thank you in advanced.

Answer 1

You cannot assign multiple rows to a scalar variable. But maybe you want to use an INSERT ... SELECT like the following? That'll insert all the results from the query together with that literals.

INSERT INTO test.wp_usermeta
            (umeta_id,
             user_id,
             meta_key,
             meta_value)
            SELECT NULL,
                   user_id,
                   '_stripe_customer_id',
                   cusid
                   FROM test.wp_usermeta wp_usermeta
                        INNER JOIN test.subs subs
                                   ON wp_usermeta.meta_value = subs.email;

You should also consider using explicit join syntax and not enclosing string literals in double quotes. That's what single quotes are for in SQL.