关于SQL子查询更新问题,求大神指导 最近做一个功能,具体是这样:订单审核,然后订单明细的每条记录的价格会自动更新到报价表的报价,下面只显示更新代码:
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)
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
------其他解决方案--------------------
能不能给出表结构和测试数据以及期待的结果 ------其他解决方案-------------------- 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
and XiaoShou_DingDan.DingDanID = 4 ------其他解决方案-------------------- 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