Mathew Jenkinson Mathew Jenkinson - 6 months ago 48
SQL Question

MySQL Insert into table with values from Inner Join

I'm trying to insert data into a inventory table, pulling in a UserID from a separate Users table to populate one of the fields.;

Inventory:
ProductID | PurchasedByUser | OtherAttributes


and a table of Users;

Users:
DBID | UserActive | UserName
1 | 1 | mathew


So far my SQL looks like:

INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
SELECT 23, U.DBID, 'Yellow'
FROM Inventory U INNER JOIN Users ud
ON U.DBID = ud.DBID AND ud.UserActive = 1 AND UserName = 'mathew'


Edit:
I want to be able to make a single SQL INSERT request to add
ProductID (INT), PurchasedByUser (Users.DBID), OtherAttributes (String) to the Inventory table without exposing the DBID and passing only the UserName field.

Answer

CHANGE U.DBID TO ud.DBID

INSERT INTO Inventory (ProductID, PurchasedByUser, OtherAttributes)
SELECT 23, ud.DBID, 'Yellow'
FROM Inventory U INNER JOIN Users ud 
ON U.PurchasedByUser = ud.DBID AND ud.UserActive = 1 AND ud.UserName = 'mathew';