update a set a.UpDownExtent=round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3) from BuyLog a left join SumStock b on a.CodeID=b.CodeID where b.States<>0 and a.UserID=1
这句在 SQL Server 里执行很正常,可是在access数据库执行就报错:语法错误(操作符丢失)在查询表达式
请问改如何改正?谢谢!
调试欢乐多
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\2052\JETSQL40.CHM JET-SQL 参考
http://download.csdn.net/source/351771 Access使用的是Jet-SQL。 JET SQL 帮助(jet4 access2000)下载地址
http://www.access911.net/index.asp?board=8&recordid=75FAB71E&tt=
from BuyLog a left join SumStock b on a.CodeID=b.CodeID
where b.States<>0 and a.UserID=1
update用直接用表名,再试试
update BuyLog set a.UpDownExtent=round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3)
from BuyLog a left join SumStock b on a.CodeID=b.CodeID
where b.States <>0 and a.UserID=1
这样还是报同样的错!
update BuyLog
set a.UpDownExtent=round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3)
from BuyLog a , SumStock b
where a.CodeID = b.CodeID and b.States <>0 and a.UserID=1
update BuyLog
set a.UpDownExtent= isnull(select round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3) from SumStock b where a.CodeID = b.CodeID and b.States <>0 and a.UserID=1 , a.UpDownExtent)
from BuyLog a
update BuyLog
set a.UpDownExtent=round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3)
from BuyLog a , SumStock b
where a.CodeID = b.CodeID and b.States <>0 and a.UserID=1update BuyLog
set a.UpDownExtent= isnull(select round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3)
from SumStock b where a.CodeID = b.CodeID and b.States <>0 and a.UserID=1 , a.UpDownExtent)
from BuyLog a
这两个还是同样报错:语法错误(操作符丢失)在查询表达式
from BuyLog a left join SumStock b on a.CodeID=b.CodeID
where b.States <>0 and a.UserID=1再试一下!a.UpDownExtent写成UpDownExtent
from BuyLog a left join SumStock b on a.CodeID=b.CodeID
where b.States <>0 and a.UserID=1 这样写语法是没有错误的!a.UpDownExtent换成了UpDownExtent
from BuyLog a left join SumStock b on a.CodeID=b.CodeID' 中。
http://topic.csdn.net/u/20080315/23/2263bf35-cade-4718-a5fc-2d677efadacf.html
正确写法为update BuyLog a left join SumStock b on a.CodeID=b.CodeID
set a.UpDownExtent=round(((b.NewValue-b.YesterdayClose)/b.YesterdayClose)*100,3)
where b.States<>0 and a.UserID=1
感谢!