create table t_wp--物品表
(
id varchar(10),--编号
mc varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
flags varchar(50)--拼音码
)
insert into t_wp select '001','复写纸','包','A4','fa4'
insert into t_wp select '002','复写纸','包','A3','fa3'
insert into t_wp select '003','钢笔','支','无','gb'create table t_kc --出入库表
(
id varchar(10),
mc varchar(50),
dw varchar(50),
gg varchar(50),
flags varchar(50),
status varchar(1)
)
insert into t_kc select null,'复写纸','包','A4','fa4','0'
insert into t_kc select null,'复写纸','包','A4','fa4','0'
insert into t_kc select null,'复写纸','包','A3','fa3','0'
insert into t_kc select null,'钢笔','支','无','gb','0'drop table t_wp
drop table t_kc
/*
我想更新t_kc表的id列,把null值更新成为t_wp表中的与t_kc表中对应
mc,dw,gg,falgs的值
结果是这样: '001','复写纸','包','A4','fa4','0'
'001','复写纸','包','A4','fa4','0'
'002','复写纸','包','A3','fa3','0'
'003','钢笔','支','无','gb','0'
*/
set id = t_wp.id
from t_kc,t_wp
where t_kc.mc = t_wp.mc and t_kc.gg = t_wp.gg
(
id varchar(10),--编号
mc varchar(50),--名称
dw varchar(50),--单位
gg varchar(50),--规格
flags varchar(50)--拼音码
)
insert into t_wp select '001','复写纸','包','A4','fa4'
insert into t_wp select '002','复写纸','包','A3','fa3'
insert into t_wp select '003','钢笔','支','无','gb'create table t_kc --出入库表
(
id varchar(10),
mc varchar(50),
dw varchar(50),
gg varchar(50),
flags varchar(50),
status varchar(1)
)
insert into t_kc select null,'复写纸','包','A4','fa4','0'
insert into t_kc select null,'复写纸','包','A4','fa4','0'
insert into t_kc select null,'复写纸','包','A3','fa3','0'
insert into t_kc select null,'钢笔','支','无','gb','0'update t_kc
set id = t_wp.id
from t_kc,t_wp
where t_kc.mc = t_wp.mc and t_kc.gg = t_wp.ggselect * from t_kcdrop table t_wp
drop table t_kcid mc dw gg flags status
---------- ------ -------- -------- ------- ------
001 复写纸 包 A4 fa4 0
001 复写纸 包 A4 fa4 0
002 复写纸 包 A3 fa3 0
003 钢笔 支 无 gb 0(所影响的行数为 4 行)