MySql Insert set with left join

Question

1)I want to insert Brand ID into Item table by using LEFT JOIN, below is the example code I tried to do , is it correct?

2)How to use INSERT SET with LEFT JOIN as I will get error message like this

select is not valid at this position, expecting EOF

INSERT INTO Brand SET Name="BMW" SELECT b.ID FROM Brand b LEFT JOIN Item i ON i.BrandID = b.ID

Brand

ID	Name
1	YSL
2	Gucci
3	Prada

Item

ID	BrandID	Name
20	3	Luxury bag
21	2	Earing

Expected Result (The Brand ID is auto increment so I no need to insert it, and I know the item name being null is weird but I can't insert two table in one query.)

Brand

ID	Name
1	YSL
2	Gucci
3	Prada
4	BMW

Item

ID	BrandID	Name
20	3	Luxury bag
21	2	Earing
22	4	null

Is the specified table data from before your INSERT? Can you also specify the expected table data after your INSERT. — jarlh
– jarlh, Commented May 9, 2022 at 8:53
Does this answer your question? It seems you cannot use set and select within an insert in MySQL Mysql insert with set and select — MarkusMulholland
– MarkusMulholland, Commented May 9, 2022 at 9:00

GigiSan · Accepted Answer · 2022-05-09 09:16:56Z

If I understand correctly, you want to INSERT the Brands the IDs of which are not yet in the table Brand.

If the above is correct, then you can simply state which value to INSERT for the columns, like this:

INSERT INTO Brand (ID, Name) SELECT DISTINCT i.BrandID, "BMW" FROM Item i LEFT JOIN Brand b ON i.BrandID = b.ID WHERE b.ID IS NULL /* This is to prevent duplicates on Brand.ID */

I want to insert Brand first and then insert the BrandID into Item table, so if I select i.ID like your answer then the Brand ID of BMW will be "22".
@Anno123 Then there was a mistake in your query, because you were joining on i.ID instead of i.BrandID. I updated my answer too. It's not an INSERT SET, but it still fulfills your requirement.

Collectives™ on Stack Overflow

MySql Insert set with left join

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related