关于update更新语句的判断有关问题
关于update更新语句的判断问题
现在要更新一个表
更新语句大致是 update A set m='xxx' where FLSUM=200 and mMemberCardId in (select MemberId from AgentList where iFranchiserState='1') 然后我还需要判断A表内的mMemberCardId在B表内的shopid是否是等于1应该在后面假如判断？
------解决方案--------------------
update a
set a.m='xxxx'
from a inner join b on b.mMemberCardId=a.mMemberCardId
where b.shopid=1 --这里假设等于1就更新，如果不等于1才更新，就改为b.shopid<>1
不过我觉得你这个描述有点拗口
------解决方案--------------------
try this,
------解决方案--------------------
你那样写也可以，但通过关联查询更好
------解决方案--------------------
是这样不：
现在要更新一个表
更新语句大致是 update A set m='xxx' where FLSUM=200 and mMemberCardId in (select MemberId from AgentList where iFranchiserState='1') 然后我还需要判断A表内的mMemberCardId在B表内的shopid是否是等于1应该在后面假如判断？
------解决方案--------------------
update a
set a.m='xxxx'
from a inner join b on b.mMemberCardId=a.mMemberCardId
where b.shopid=1 --这里假设等于1就更新，如果不等于1才更新，就改为b.shopid<>1
不过我觉得你这个描述有点拗口
------解决方案--------------------
try this,
update A
set m='xxx'
where FLSUM=200 and mMemberCardId in
(select MemberId from AgentList
where iFranchiserState='1')
and exists
(select 1 from B表 b
where b.mMemberCardId=a.mMemberCardId and b.shopid=1)
------解决方案--------------------
你那样写也可以，但通过关联查询更好
update A
set m='xxx'
from A
inner join AgentList on A.mMemberCardId=AgentList.MemberId
where A.FLSUM=200 and AgentList.iFranchiserState='1' --(如果是shopid是否是等于1,那么where A.FLSUM=200 and AgentList.shopid='1')
------解决方案--------------------
是这样不：
update A set m='xxx'
where FLSUM=200
and mMemberCardId in (select MemberId from AgentList where iFranchiserState='1')
and exists(select 1 from B where b.shopid = 1 and b.mMemberCardId = a.mMemberCardId)