2 u014139945 u014139945 于 2014.05.09 10:07 提问

sql 如何在建立一个存储过程时更新两张表的数据?

create proc proc2
(
@OrderID int,
@BookID varchar(20),--更新 orderInfo quantity;更新 orderSheet payment
@BookStock int
)
as
update orderSheet
set payment = b.newpay
FROM orderInfo,orderSheet,(select (payment + price * @BookStock) newpay
FROM orderSheet,book,(update orderInfo
SET quantity = a.qq
FROM (select (o1.quantity + @BookStock) qq
from orderInfo o1
WHERE o1.orderid = @OrderID
AND o1.bookid = @BookID) a) aa
WHERE orderSheet.oid = aa.orderid
AND aa.bookid = book.bid) b
where orderInfo.orderid = @OrderID
AND orderInfo.bookid = @BookID
AND orderSheet.oid = orderInfo.orderid

求问:这样错在哪里?正确的又应该怎样做?

2个回答

dotnetstudio
dotnetstudio   Rxr 2014.05.09 15:12

你这个嵌套太多了吧,为什么不分步骤来做呢

u014139945
u014139945 分步骤?敢问该怎样修改才好?
3 年多之前 回复
feiyun0112
feiyun0112   Rxr 2014.05.09 17:33

create proc proc2
(
@OrderID int,
@BookID varchar(20),--更新 orderInfo quantity;更新 orderSheet payment
@BookStock int
)
as
begin

update orderInfo
SET quantity =(quantity + @BookStock)
WHERE orderid = @OrderID
AND bookid = @BookID

update orderSheet
set payment = orderSheet.payment+orderInfo.price*orderInfo.quantity
FROM orderInfo
where orderInfo.orderid = @OrderID
AND orderInfo.bookid = @BookID
AND orderSheet.oid = orderInfo.orderid

end
go

u014139945
u014139945 在此谢过大神了……原来这里是运用了begin...end结构。存储过程会执行这个结构中的内容直至其结束才算完成。……不知这样说可对?
3 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!