Question

VB中SQL语言对一个表进行添加数据使用另一个表的内容

表1(mylotdata):
TGT_LOT_NBR
1345
1567

表2(myrejectdata):
LOT_NBR REJECT_CATG REJECT_QTY
1345 PA 131
1345 OP 141
1567 PA 20
1567 OP 10
使用SQL语言把表2内容添加到表1,如下:
TGT_LOT_NBR PA OP
1345 131 141
1567 20 10
下面是我做的一些动作,都有问题:
Private Sub Command3_Click()
Dim db As DAO.Database
Set db = OpenDatabase(App.Path & "\123.mdb", False, False, ";pwd=kiss")
db.Execute "ALTER TABLE mylotdata ADD COLUMN PA int" '我先试PA这种情况先在mylotdata表中加入例PA
db.Execute "update mylotdata set PA = myrejectdata.REJECT_QTY from myrejectdata where myrejectdata.LOT_NBR = mylotdata.TGT_LOT_NBR and myrejectdata.REJECT_CATG ='PA'"
'上面这种试过不能通过,始终报警下面为我又用的一种方式
db.Execute "insert into mylotdata (PA) select REJECT_QTY from myrejectdata where mylotdata.TGT_LOT_NBR = myrejectdata.LOT_NBR and myrejectdata.REJECT_CATG ='PA'"
'以上这种方式也不能实现,由于数据库是上万条记录,所以只能求助SQL语言解决.
end sub

希望大家帮助解决一下,万分感谢!!

Answer 1

Private Sub Command3_Click()
Dim db As DAO.Database
Set db = OpenDatabase(App.Path & "\123.mdb", False, False, ";pwd=kiss")
db.Execute "ALTER TABLE mylotdata ADD PA int,OP int"
db.Execute "update mylotdata inner join myrejectdata on mylotdata.TGT_LOT_NBR=myrejectdata.LOT_NBR set mylotdata.PA = myrejectdata.REJECT_QTY where myrejectdata.REJECT_CATG ='PA'"
db.Execute "update mylotdata inner join myrejectdata on mylotdata.TGT_LOT_NBR=myrejectdata.LOT_NBR set mylotdata.OP = myrejectdata.REJECT_QTY where myrejectdata.REJECT_CATG ='OP'"
db.close
end sub