问题描述
我正在就以下问题寻求帮助:我有两张桌子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)
这篇关于使用另一个选择查询的结果将数据插入表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!