MySQL使用内部联接的值插入表中 [英] MySQL Insert into table with values from Inner Join

问题描述

我正在尝试将数据插入清单表，从一个单独的用户表中提取一个UserID来填充其中一个字段.

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

和一个用户表；

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

到目前为止，我的SQL看起来像:

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'

我希望能够发出单个SQL INSERT请求来添加产品ID(INT)，purchasedByUser(Users.DBID)，OtherAttributes(String)到库存表，而无需暴露DBID并仅传递UserName字段.

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.

推荐答案

将U.DBID更改为ud.DBID

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';

这篇关于MySQL使用内部联接的值插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！