CODEWITHSUNDEEP
mysqlsqldatabaseselectinsert
srirachablender
srirachablender

Reputation: 89

How do I combine two SQL statements into one statement?

I have an INSERT SQL statement where one of the values of the row I insert into the table is the result of another SELECT SQL query. I was wondering how I can appropriately combine these two statements into one. Thank you.

The SELECT statement:

    SELECT mpca.creative_attribute_id
    FROM media_property_creative_attribute as mpca
    WHERE mpca.media_property_id=54 
        and mpca.media_property_creative_attribute_external_id=101;

The result of this query will be a single value, which I will denote in the next INSERT statement as [creative_attribute_id].

The INSERT statement:

    INSERT INTO ad_creative_attribute_list (ad_id, creative_attribute_id)
    VALUES (12, [creative_attribute_id])
    WHERE NOT EXISTS (
        SELECT *
        FROM ad_creative_attribute_list as acal
        WHERE acal.ad_id=12
            AND acal.creative_attribute_id=[creative_attribute_id])

Can this be combined into one statement?

Upvotes: 0

Views: 191

Answers (2)

radar
radar

Reputation: 13425

You can use insert into select syntax

Not exists will be correlated sub query

INSERT INTO ad_creative_attribute_list (ad_id, creative_attribute_id)
    SELECT 12, mpca.creative_attribute_id
FROM media_property_creative_attribute as mpca
WHERE mpca.media_property_id=54 
    and mpca.media_property_creative_attribute_external_id=101
   And  NOT EXISTS (
    SELECT 1 
    FROM ad_creative_attribute_list as acal
    WHERE acal.ad_id=12
        AND acal.creative_attribute_id= mpca.creative_attribute_id)

Upvotes: 0

user2941651
user2941651

Reputation:

UPDATED

I've updated the query according to the suggestion - hope it will fit your case:

INSERT INTO 
    ad_creative_attribute_list (ad_id, creative_attribute_id)
SELECT 
    12, mpca.creative_attribute_id
FROM 
    media_property_creative_attribute as mpca
WHERE 
    mpca.media_property_id = 54 
    and mpca.media_property_creative_attribute_external_id = 101
    AND NOT EXISTS (SELECT acal.creative_attribute_id
                    FROM ad_creative_attribute_list as acal
                    WHERE acal.ad_id = 12
                    AND mpca.creative_attribute_id = acal.creative_attribute_id);

Upvotes: 1

Related Questions