How to left outer join and insert new data at the same time into a mysql table-CodePudding

I want to insert in new data at same time and merged with outer left join values from another table. I have tried by creating the values first and merge by using union for the selection of two columns which is left outer joined to the table. What other ways can I merge left outer join and newly created values into the table?

INSERT INTO Dessert(pie, strawberry)
VALUES('chocolate', '5')
UNION
SELECT cream, iceCream, null
FROM Craving
LEFT JOIN Dessert
ON Craving.cream = Dessert.cream AND Craving.iceCream = Dessert.iceCream

The table I am trying to create

Dessert                             Craving 
cream iceCream pie strawberry       cream iceCream

CodePudding user response：

You should be able to construct a select statement for the new values and union that to a select from the existing data:

INSERT INTO Dessert(pie, strawberry)
SELECT 'chocolate' as cream, '5' as iceCream
UNION
SELECT cream, iceCream
FROM Craving LEFT JOIN Dessert
ON Craving.cream = Dessert.cream AND Craving.iceCream = Dessert.iceCream

BUT your schema does not make a lot of sense to me. You have fields in Dessert called pie, strawberry, cream and ice cream? And you are selecting the fields cream and iceCream but inserting them into pie and strawberry? And iceCream is a numeric field stored as a string?

It might be helpful if you could share the table structures you have. You should be able to export the CREATE statements from your mySQL editor. Also, it would be good if you could share some sample data.