最近做一个功能,具体是这样:订单审核,然后订单明细的每条记录的价格会自动更新到报价表的报价,下面只显示更新代码:
update XiaoShou_BaoJia set
JiaGe= (SELECT XiaoShou_DingDanMingXi.DanJia
FROM XiaoShou_DingDanMingXi LEFT OUTER JOIN
XiaoShou_DingDan ON XiaoShou_DingDanMingXi.DingDanID=XiaoShou_DingDan.DingDanID LEFT OUTER JOIN
XiaoShou_BaoJia ON XiaoShou_BaoJia.WuPinID = XiaoShou_DingDanMingXi.WuPinID AND
XiaoShou_BaoJia.GuiGeID = XiaoShou_DingDanMingXi.GuiGeID AND
XiaoShou_DingDan.KeHuID = XiaoShou_BaoJia.KeHuID
WHERE XiaoShou_DingDan.DingDanID = 4 AND XiaoShou_BaoJia.JiaGe IS NOT NULL)WHERE XiaoShou_DingDan.DingDanID = 4 --不能通过执行最后那个WHERE语句因为XiaoShou_BaoJia 没有关联XiaoShou_DingDan,所以不能通过,如果没有这个条件语句,那么就会更新报价表里的所有报价,但我不懂怎么把这个语句写上去,应该有方法的,所以在这求助。
update XiaoShou_BaoJia set
JiaGe= (SELECT XiaoShou_DingDanMingXi.DanJia
FROM XiaoShou_DingDanMingXi LEFT OUTER JOIN
XiaoShou_DingDan ON XiaoShou_DingDanMingXi.DingDanID=XiaoShou_DingDan.DingDanID LEFT OUTER JOIN
XiaoShou_BaoJia ON XiaoShou_BaoJia.WuPinID = XiaoShou_DingDanMingXi.WuPinID AND
XiaoShou_BaoJia.GuiGeID = XiaoShou_DingDanMingXi.GuiGeID AND
XiaoShou_DingDan.KeHuID = XiaoShou_BaoJia.KeHuID
WHERE XiaoShou_DingDan.DingDanID = 4 AND XiaoShou_BaoJia.JiaGe IS NOT NULL)WHERE XiaoShou_DingDan.DingDanID = 4 --不能通过执行最后那个WHERE语句因为XiaoShou_BaoJia 没有关联XiaoShou_DingDan,所以不能通过,如果没有这个条件语句,那么就会更新报价表里的所有报价,但我不懂怎么把这个语句写上去,应该有方法的,所以在这求助。
解决方案 »
- sql语句查询
- 关于set nocount on;的问题
- SQLserver2000中一条SQL语句
- log explorer恢复删除的表
- *****************请教关于sqlserver2005 分区表的问题************************
- 只有MDF恢复数据库,出现“未能读取并闩锁页 (5:681)(用闩锁类型 SH)”是什么错?
- 高手进:求最值,并指出最值出现位置!(续)
- (求助!!急)使用sql如何实现数据分割功能(以","为分割符号,每个字符大小数量不定)
- 用like来判断一列是否是数字,包括小数点,负数
- 求教物资进库表和物资库存表的问题!
- sqlserver2008 数据误删除,没有备份 如何恢复以前数据
- 求一个sql语句实现下面效果
update XiaoShou_BaoJia set
JiaGe=XiaoShou_DingDanMingXi.DanJia
FROM XiaoShou_DingDanMingXi LEFT OUTER JOIN
XiaoShou_DingDan ON XiaoShou_DingDanMingXi.DingDanID=XiaoShou_DingDan.DingDanID LEFT OUTER JOIN
XiaoShou_BaoJia ON XiaoShou_BaoJia.WuPinID = XiaoShou_DingDanMingXi.WuPinID AND
XiaoShou_BaoJia.GuiGeID = XiaoShou_DingDanMingXi.GuiGeID AND
XiaoShou_DingDan.KeHuID = XiaoShou_BaoJia.KeHuID
WHERE XiaoShou_DingDan.DingDanID = 4 AND XiaoShou_BaoJia.JiaGe IS NOT NULL
FROM XiaoShou_DingDanMingXi LEFT OUTER JOIN
XiaoShou_DingDan ON XiaoShou_DingDanMingXi.DingDanID=XiaoShou_DingDan.DingDanID LEFT OUTER JOIN
XiaoShou_BaoJia ON XiaoShou_BaoJia.WuPinID = XiaoShou_DingDanMingXi.WuPinID AND
XiaoShou_BaoJia.GuiGeID = XiaoShou_DingDanMingXi.GuiGeID AND
XiaoShou_DingDan.KeHuID = XiaoShou_BaoJia.KeHuID
WHERE XiaoShou_DingDan.DingDanID = 4 AND XiaoShou_BaoJia.JiaGe IS NOT NULL
and XiaoShou_DingDan.DingDanID = 4
set JiaGe= XiaoShou_DingDanMingXi.DanJia
FROM XiaoShou_DingDanMingXi
LEFT OUTER JOIN
XiaoShou_DingDan ON XiaoShou_DingDanMingXi.DingDanID=XiaoShou_DingDan.DingDanID
LEFT OUTER JOIN
XiaoShou_BaoJia ON XiaoShou_BaoJia.WuPinID = XiaoShou_DingDanMingXi.WuPinID AND
XiaoShou_BaoJia.GuiGeID = XiaoShou_DingDanMingXi.GuiGeID AND
XiaoShou_DingDan.KeHuID = XiaoShou_BaoJia.KeHuID
WHERE XiaoShou_DingDan.DingDanID = 4 AND XiaoShou_BaoJia.JiaGe IS NOT NULL
and XiaoShou_DingDan.DingDanID = 4改下红字部分