使用另一个选择查询的结果将数据插入表中

我正在就以下问题寻求帮助:我有两张桌子Table_1 列分别是itemid、locationid、quantity

Table_2 列分别是 itemid、location1、location2、location3

我想将数据从Table_1(只有quantity 列)复制到Table_2(到location1 列).itemid 在两个表中是相同的(Table_1 有重复的项目 ID)所以这就是我想复制到一个新表并将所有数量保留在一行中的原因每个位置作为一列.我正在使用以下查询，但它不起作用

插入表_2(位置1)(选择数量从表_1WHERE locationid = 1 AND Table_1.locationid = Table_2.locationid)

解决方案

如果 table_2 为空，则尝试以下插入语句:

insert into table_2 (itemid,location1)select itemid,quantity from table_1 where locationid=1

如果 table_2 已经包含 itemid 值，那么试试这个更新语句:

update table_2 set location1=(从 table_1 中选择数量，其中 locationid=1 和 table_1.itemid = table_2.itemid)

I am seeking help on the following issue: I have two tables Table_1 columns are itemid, locationid, quantity

Table_2 columns are itemid, location1, location2, location3

I want to copy data from Table_1 (only quantity column) into Table_2 (into location1 column). The itemid are same in both the tables(Table_1 has duplicate item id's) so that's the reason I want to copy to a new table and keep all quantity in one single row with each location as a column. I am using the below query but it doesn't work

INSERT INTO 
Table_2(location1) 
(
 SELECT qty 
 FROM Table_1 
 WHERE locationid = 1 AND Table_1.locationid = Table_2.locationid
)

解决方案

If table_2 is empty, then try the following insert statement:

insert into table_2 (itemid,location1) 
select itemid,quantity from table_1 where locationid=1

If table_2 already contains the itemid values, then try this update statement:

update table_2 set location1=
(select quantity from table_1 where locationid=1 and table_1.itemid = table_2.itemid)