Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Join with Insert combined?

Tags:

join

mysql

insert

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!

like image 600
Lee Armstrong Avatar asked Jul 28 '11 16:07

Lee Armstrong


1 Answers

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
like image 134
Joe Stefanelli Avatar answered Oct 14 '22 02:10

Joe Stefanelli