CODEWITHSUNDEEP
mysqlsqllaravel
sdexp
sdexp

Reputation: 776

INSERT INTO SELECT - More than 1 row

I'm used to having an insert into select where all the values are taken from other tables. In this case, I am trying to duplicate an existing feed. I have one pivot table that needs the values of one feed copying to the new one. There are only two columns in the table but I'm using one value from the SQL and the id of the new feed does not change.

 DB::statement('INSERT INTO feed_media_permissions 
            (feed_id, optin_media_id)
            VALUES 
            ('. $feed->id .', 
            (SELECT forig.optin_media_id AS optin_media_id
            FROM feed_media_permissions as forig 
            WHERE forig.feed_id = ' . $existing_feed_id . ')
            )');

Gives this error...

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row (SQL: INSERT INTO feed_media_permissions (feed_id, optin_media_id) VALUES (158, (SELECT forig.optin_media_id AS optin_media_id FROM feed_media_permissions as forig WHERE forig.feed_id = 156) ))

There can be 0-4 rows to copy each time. Any way around this?

Upvotes: 2

Views: 1279

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

The immediate cause of your error is that the subquery returns more than one row. A possible fix might be to just use the select with multiple rows as the source of the data for insertion. That is, use an INSERT INTO ... SELECT construct:

INSERT INTO feed_media_permissions (feed_id, optin_media_id)
SELECT ?, forig.optin_media_id
FROM feed_media_permissions AS forig 
WHERE forig.feed_id = ?;

Ideally you should be using a PHP prepared statement here. To the first placeholder you would bind $feed->id, and to the second one you would bind $existing_feed_id.

Upvotes: 4

Jerodev
Jerodev

Reputation: 33186

Your $feed->id should be in the subquery to make sure it is on every row that is returned.

DB::statement('INSERT INTO feed_media_permissions 
    (feed_id, optin_media_id)
    VALUES 
    (SELECT '. $feed->id .' as feed_id, forig.optin_media_id AS optin_media_id
    FROM feed_media_permissions as forig 
    WHERE forig.feed_id = ' . $existing_feed_id . ')
');

You should also use bindings to prevent sql injections:

DB::statement('INSERT INTO feed_media_permissions 
    (feed_id, optin_media_id)
    VALUES 
    (SELECT ? as feed_id, forig.optin_media_id AS optin_media_id
    FROM feed_media_permissions as forig 
    WHERE forig.feed_id = ?)
', [$feed->id, $existing_feed_id]);

Upvotes: 1

Related Questions