CODEWITHSUNDEEP
mysqljoininsert
Lee Armstrong
Lee Armstrong

Reputation: 11452

MySQL Join with Insert combined?

Is it possible to have an INSERT from a reference table if something exists there?

Say this is the query to insert some data...

INSERT INTO  `Test`.`Items` (
`Name` ,
`Type` ,
`Number`
)
VALUES (
'Pork',  'Sausage', 6
);

And that I have a lookup table called "Lookup" that contains two columns. "Type" and "Number". What I want is that if something exists in the "Lookup" table in this instance for Type Sausage then to pull in the "Number" field from the lookup table instead of inserting the 6 in the INSERT statement.

enter image description here

Hope that is clear!

Upvotes: 6

Views: 12481

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

INSERT INTO Test.Items 
    (Name, Type, Number)
    SELECT 'Pork', 'Sausage', COALESCE(l.Number, 6)
        FROM Lookup l
        WHERE l.Type = 'Sausage'

EDIT: Additional samples based on comments below.

Using UNION ALL to string inserts together:

INSERT INTO Test.Items 
    (Name, Type, Number)
    SELECT 'Pork', 'Sausage', COALESCE(l.Number, 6)
        FROM Lookup l
        WHERE l.Type = 'Sausage'
    UNION ALL
    SELECT 'Halibut', 'Fish', COALESCE(l.Number, 7)
        FROM Lookup l
        WHERE l.Type = 'Fish'

Using a temporary table:

CREATE TEMPORARY TABLE tmpItems (
    Name VARCHAR(255),
    Type VARCHAR(255),
    Number INT
)

INSERT INTO tmpItems
    (Name, Type, Number)
    VALUES ('Pork', 'Sausage', 6)
INSERT INTO tmpItems
    (Name, Type, Number)
    VALUES ('Halibut', 'Fish', 7)

INSERT INTO Test.Items 
    (Name, Type, Number)
    SELECT t.Name, t.Type, COALESCE(l.Number, t.Number)
        FROM tmpItems t
            LEFT JOIN Lookup l
                ON t.Type = l.Type

Upvotes: 8

Related Questions