【求助】我想把查不出的数据更新到另个一表 sql语句合并求高手

超维电脑科技 2013-10-19 10:09:38

select bmgz_002,bmgz_003,sum(bmjj_011)+bmgz_007 as gz_009 ,sum(bmjj_011)+bmgz_007+bmgz_010+bmgz_011 as gz_012,sum(bmjj_011)+bmgz_007+bmgz_010+bmgz_011-bmgz_013-bmgz_014-bmgz_015-bmgz_016-bmgz_017 as gz_018 from bmgz,bmjj where bmgz_002 =bmjj_002 and bmgz_003 = bmjj_008 and bmgz_003 = '2013@05' and bmgz_002 in (select bmjj_002 from bmjj where bmjj_003 = '80027' and bmjj_004 = '车后领双针' and bmjj_008 ='2013@05') group by bmgz_002,bmgz_003,bmgz_007,bmgz_010,bmgz_011,bmgz_013,bmgz_014,bmgz_015,bmgz_016,bmgz_017

--要更新的是
update bmgz set bmgz_009 = gz_009(上面查出来的), bmgz_010 = gz_009 where where bmjj_003 = '80027' and bmjj_004 = '车后领双针' and bmjj_008 ='2013@05')


更新语句不知道怎么写 ,求高手合并 分数可以加
...全文
153 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
超维电脑科技 2013-10-19
  • 打赏
  • 举报
回复
解决了现在结贴,谢谢各位高手
LongRui888 2013-10-19
  • 打赏
  • 举报
回复



with t
as
(  -- 这个是你上面的查询,没改动
select bmgz_002,bmgz_003,sum(bmjj_011)+bmgz_007 as gz_009 ,
sum(bmjj_011)+bmgz_007+bmgz_010+bmgz_011 as gz_012,
sum(bmjj_011)+bmgz_007+bmgz_010+bmgz_011-bmgz_013-bmgz_014-bmgz_015-bmgz_016-bmgz_017 as gz_018  from bmgz,bmjj where bmgz_002 =bmjj_002 and bmgz_003 = bmjj_008 and bmgz_003 = '2013@05' and bmgz_002 in (select bmjj_002 from bmjj where bmjj_003 = '80027' and bmjj_004 = '车后领双针' and bmjj_008 ='2013@05') 
group by bmgz_002,bmgz_003,bmgz_007,bmgz_010,bmgz_011,bmgz_013,bmgz_014,bmgz_015,bmgz_016,bmgz_017
)

--要更新的是
update bmgz 
set bmgz_009 = t.gz_009,
    bmgz_010 = t.gz_009
from bmgz b
inner join t
        on t.bmgz_002 = b.bmgz_002   --关联条件,按照你的需要加
           and t.bmgz_003 = b.bmgz_003
where b.bmjj_003 = '80027' 
      and b.bmjj_004 = '车后领双针'
      and b.bmjj_008 ='2013@05'

Andy__Huang 2013-10-19
  • 打赏
  • 举报
回复
查询不出的数据更新? 应该说没有符合条件的更新吧?应该用not in或not exists
超维电脑科技 2013-10-19
  • 打赏
  • 举报
回复
引用 1 楼 AcHerat 的回复:

我试下 ,先谢谢啦
AcHerat 2013-10-19
  • 打赏
  • 举报
回复

--sql 2005/2008/2012

;with cte as
(
    select ... --你的查询语句
)

update t
set t.[] = e.[],...
from bmgz t,cte e
where t.[] = e.[] --两表关联条件,就是你查询到的和这个表的关联条件
    and t.[] = ...  --其他条件

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧