0

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
Anno123
  • 67
  • 6
  • Is the specified table data from _before_ your INSERT? Can you also specify the expected table data _after_ your INSERT. – jarlh May 09 '22 at 08:53
  • Which dbms are you using? – jarlh May 09 '22 at 08:54
  • 1
    Does this answer your question? It seems you cannot use set and select within an insert in MySQL [Mysql insert with set and select](https://stackoverflow.com/questions/54037336/mysql-insert-with-set-and-select) – MarkusMulholland May 09 '22 at 09:00

1 Answers1

0

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 */
GigiSan
  • 1,170
  • 2
  • 19
  • 30
  • 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 May 09 '22 at 09:15
  • @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. – GigiSan May 09 '22 at 09:18