Inserting multiple data into table with SELECT and JOIN statement

Question

I would like to insert data into table with one of the columns being populated by a select query. This is my SQL statement:

INSERT INTO books ( gid, type, membernumber,  changedate, user)
  VALUES( '2b4493e8-cae8-4624-8177-fcc96553c5be',
          2,
          (SELECT member FROM mem m JOIN group g ON m.uid = g.sgroup),
          TIMESTAMP '2017-01-03 00:00:00.000',
          'mark'
        );

What I want to achieve is for each insert, the membernumber will be taken from the mem table base on the JOIN it has with the group table.

When I execute this statement, I get the error:

More than one row returned by a subquery used as an expression

So for instance if the join will produce 20 rows then 20 records will be inserted with each member value replacing the membernumber in the insert for the 20 rows.

Update

I also have another query where two columns have to be replaced with SELECT-JOIN statements. @Tim has given me an example of one column. I want an example with two columns been filled with two SELECT-JOIN statements.

Tim Biegeleisen · Accepted Answer · 2018-06-05 08:04:53Z

2

You may rephrase this as an INSERT INTO ... SELECT:

INSERT INTO books (gid, type, membernumber, changedate, user)
SELECT
    '2b4493e8-cae8-4624-8177-fcc96553c5be',
    2,
    member,
    '2017-01-03 00:00:00.000',
    'mark'
FROM mem m
INNER JOIN `group` g
    ON m.uid = g.sgroup;

This should fix your syntax error, but now you have to decide whether this insert makes any sense without a WHERE clause. You are hard coding almost every field for each member.

answered Jun 5, 2018 at 8:04

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

2 Comments

Eddy Freeman Over a year ago

thanks for your reply. My next question is, is it possible to do the same with 2 or more Select-Join statements in the same query because i have another issue similar to the first but now with 3 Select-Join statement. Can you show me an example using the query above but lets assume "user" will have SELECT-Join like membernumber. Thanks

Tim Biegeleisen Over a year ago

@EddyFreeman I don't completely follow your comment, but it looks like a non trivial departure from this question. You may want to ask a new question.

Collectives™ on Stack Overflow

Inserting multiple data into table with SELECT and JOIN statement

Update

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related