Question

SQl update 多表关联 问题

A 表 字段

A.spid, A.shl, A.fdbs
A.shky = '002',
A.rq= '2010-04-06',
A.ontime > '17:38:18',

B 表 字段

B.spid,B.rq,B.shl,B.fdbs

要求实现两张表关联字段 做减法
set
A.shl = A.shl - B.shl
where
A.spid = B.spid and
A.fdbs = B.fdbs and
A.rq = B.rq and
A.ontime > '17:37:18' and
A.shky = '002' and
A.fdbs = 'HKD'
update A
set A.shl = A.shl =A.shl - B.shl --> set A.shl =A.shl - B.shl 不好意思，属于粘贴错误
from retmxls A,daysphz B
where A.spid = B.spid and
A.fdbs = B.fdbs and
A.rq = B.rq and
A.ontime > '17:37:18' and
A.shky = '002' and
A.fdbs = 'HKD'

我也是写的如一楼，但是返回‘第二行 = 附近 语法错误 ’ ------仍然提示该错误

Answer 1

update A
set A.shl = A.shl - B.shl
from retmxls A,daysphz B
where A.spid = B.spid and
A.fdbs = B.fdbs and
A.rq = B.rq and
A.ontime > '17:37:18' and
A.shky = '002' and
A.fdbs = 'HKD'

这个是SqlServer的语法

Answer 2

你的意思是不是，增加明细表的时候，单号表的amount自动更新。
这样的话，写一个触发器就可以了。
create trigger 触发器名称
on 明细
for insert
as
begin
update 单号 set amount = a.amount + b.amount
from 单号 a,inserted b
where a.billid = b.billid
end

Answer 3

update a
set
A.shl = A.shl - B.shl
from a,b
where
A.spid = B.spid and
A.fdbs = B.fdbs and
A.rq = B.rq and
A.ontime > '17:37:18' and
A.shky = '002' and
A.fdbs = 'HKD'

你是要这个？？表的别名自己加下

set A.shl = A.shl =A.shl - B.shl
都告诉你第二行错误了
A.shl = A.shl =A.shl - B.shl 是什么写法