INSERT INTO tb_药品销售 ( 药品编号,药品名称)
(
SELECT 药品编号,药品名称
FROM tb_药品登记
WHERE 药品编号 NOT IN
(
SELECT 药品编号
FROM tb_药品销售
)
)
另外一种写法:
INSERT INTO tb_药品销售 (药品编号, 药品名称)
SELECT
药品编号,
药品名称
FROM tb_药品登记
WHERE 药品编号 NOT IN (SELECT
药品编号
FROM tb_药品销售)
---------------------------------------------------------------
插入一些测试帮助数据到一个表时,可以用这种方式:
例如:
INSERT INTO [dbo].[MyDuanDian] ( [file_location]
,[rn])
select '.\\HaixinIWMS.Service\\GoodsOut\\GoodsOutDetailService.cs' , 23 union all
select '.\\HaixinIWMS.Service\\GoodsOut\\GoodsOutDetailService.cs', 43 union all
select '.\\HaixinIWMS.Service\\GoodsOut\\GoodsOutDetailService.cs', 127 union all
select '.\\HaixinIWMS.Service\\GoodsOut\\GoodsOutDetailService.cs', 154 union all
select '.\\HaixinIWMS.Service\\GoodsOut\\GoodsOutDetailService.cs', 166
-------------------------
UPDATE tb_药品销售
SET 药品名称=(
SELECT 药品名称
FROM tb_药品登记
WHERE tb_药品登记.药品编号=tb_药品销售.药品编号)
另外一种写法update set from:
update table1
set field1=table2.field1,
field2=table2.field2
from table2
where table1.id=table2.id
再另外一种写法:
update a
set a.CalZF=(a.shoupan-b.shoupan)/b.shoupan
from PanHouData2_Copy a inner join PanHouData2_Copy b
on a.daima=b.daima and a.DaiMaRowID=b.DaiMaRowID+1
sql server 写法参考:https://www.cnblogs.com/yclizq/p/14320940.html
Oracle写法:
UPDATE NS_NATION_BODY_IMP t1
SET (LEGAL_MEASUREMENT,
SECEND_LEGAL_MEASUREMENT) =
(SELECT t2.UNIT_1,
t2.UNIT_2
FROM BASE_GOODSCODE t2
WHERE t1.COMMODITY_CODE = t2.CODE)
WHERE EXISTS
(SELECT 1 FROM BASE_GOODSCODE t2 WHERE t1.COMMODITY_CODE = t2.CODE)
mysql写法(可以同时更新两个表):
UPDATE erpcheckinventorydetail a JOIN LotInfo b
ON a.CHARG=b.CHARG and a.WERKS=b.WERK and a.LGORT=b.LGORT and a.MATNR=b.MATNR
SET a.CJTJD='222',b.CHARG='12'
where b.PROCESS=2 and b.PROCESSSTATUS=21 and a.CJTJD='222'
--------------------------------------------
DELETE tb_药品销售
WHERE 药品编号 IN
(
SELECT 药品编号
FROM tb_药品登记
WHERE 生产厂家='长春一通')
另外一种方法:
DELETE tb_药品销售
FROM tb_药品登记
WHERE tb_药品销售.药品编号 = tb_药品登记.药品编号
AND tb_药品登记.生产厂家 = '长春一通'