update Items
set Amount = Amount + pdSumAmount , aCost = aCost + pdSumCost
from Items INNER JOIN Temp001 ON Items.MatrID = Temp001.MatrIDupdate Items
set Amount = Amount + pdSumAmount , aCost = aCost + pdSumCost
from Items , Temp001 Where Items.MatrID = Temp001.MatrID其中 pdSumAmount pdSumCost 是Temp001的字段上面两个语句在Delphi6+Access2002+Ado中都不行,报错"语法错误 操作符丢失",但是在sql 2000中医点问题都没有。
是不是access不是跨表更新 update ?
set Amount = Amount + pdSumAmount , aCost = aCost + pdSumCost
from Items INNER JOIN Temp001 ON Items.MatrID = Temp001.MatrIDupdate Items
set Amount = Amount + pdSumAmount , aCost = aCost + pdSumCost
from Items , Temp001 Where Items.MatrID = Temp001.MatrID其中 pdSumAmount pdSumCost 是Temp001的字段上面两个语句在Delphi6+Access2002+Ado中都不行,报错"语法错误 操作符丢失",但是在sql 2000中医点问题都没有。
是不是access不是跨表更新 update ?
1。两个表用Where子句关联,然后更新为何出错?
update a set a.fb=b.fb where a.fa=b.fa
2。共有billlist和B_see两个表。要把B_see中的值写到billlist中
我把前缀去掉了,也还报错.SQL语句如下
update BillList set billList.ProdNewprice=B_see.a ,billList.ProdnewMoney=B_see.b,billList.ProdDiscount=B_see.c
from BillList,B_see
where billList.Id=B_see.Id
回答:
这个问题经常发生在从其他中大型数据库开发者转到用 jet db 4 的Access的时候
Access中不允许用where tableA.fieldA = TableB.fieldA 来做update的连接,这是T-sql的语法,在当初教T-sql的时候你们老师应该说过这句语句使用的注意要点吧?
必须把t-sql的语法转成Asni的语法,用 join 来做。UPDATE a INNER JOIN b ON a.fa = b.fa SET a.fb = b.fb;
What are you doing !!!!
qinmaofan (采菊南山下【抵制日货从我做起】)
试试这个
update Items, Temp001
set Items.Amount =Items.Amount +Temp001.pdSumAmount , Items.aCost =Items.aCost + Temp001.pdSumCost from Items INNER JOIN Temp001 ON Items.MatrID = Temp001.MatrIDupdate Items,Temp001
set Items.Amount = Items.Amount +Temp001.pdSumAmount , Items.aCost =Items.aCost +Temp001.pdSumCost from Items , Temp001 Where Items.MatrID = Temp001.MatrID
-------------------------------------------------------------------
我的多表更新就是这么做的在ACCESS中成功,你的没有调试过,自己试试吧。
Shiyl((云淡)||(风清)) 的写法我觉得不是很标准。我改之后:UPDATE Items INNER JOIN Temp001
ON Items.MatrID = Temp001.MatrID
SET Amount = Amount + pdSumAmount , aCost = aCost + pdSumCost