CODEWITHSUNDEEP
mysqlmagento
BenOfTheNorth
BenOfTheNorth

Reputation: 2872

Insert from sub queries

I'm trying to insert based on the results of a sub query, but I keep getting the same error:

Operand should contain 1 column(s)

Here's the query:

INSERT INTO customer_entity_int

(attribute_id, entity_type_id, entity_id, `value`)

VALUES (14, (
    SELECT entity_type_id, entity_id, `value`
    FROM customer_entity_int
    WHERE attribute_id = 13
    AND entity_id NOT
    IN (
        SELECT entity_id
        FROM customer_entity_int
        WHERE attribute_id = 14
    )
))

How can I select more than one column for my insert?

Upvotes: 1

Views: 102

Answers (3)

himadri
himadri

Reputation: 638

Please try the following code.....

INSERT INTO customer_entity_int
(attribute_id, entity_type_id, entity_id, `value`)
SELECT 14, entity_type_id, entity_id, `value`
FROM customer_entity_int
WHERE attribute_id = 13
AND entity_id NOT
IN (
    SELECT entity_id
    FROM customer_entity_int
    WHERE attribute_id = 14
   )

Upvotes: 0

Taryn
Taryn

Reputation: 247710

You will want to use INSERT INTO...SELECT FROM instead of the INSERT INTO..VALUES:

INSERT INTO customer_entity_int (attribute_id, entity_type_id, entity_id, `value`)
SELECT 14, entity_type_id, entity_id, `value`
FROM customer_entity_int
WHERE attribute_id = 13
  AND entity_id NOT IN (SELECT entity_id
                        FROM customer_entity_int
                        WHERE attribute_id = 14)

You can include a static value in your SELECT list for your attribute_id

Upvotes: 3

Sergio
Sergio

Reputation: 6948

Try dis:

INSERT INTO customer_entity_int

(attribute_id, entity_type_id, entity_id, `value`)

    SELECT 14, entity_type_id, entity_id, `value`
    FROM customer_entity_int
    WHERE attribute_id = 13
    AND entity_id NOT
    IN (
        SELECT entity_id
        FROM customer_entity_int
        WHERE attribute_id = 14
    )

Upvotes: 2

Related Questions