求sql语句?(急)

wangjungui_1983 2010-10-17 04:24:49
比较表Base_Gx_ProductsInfo中ProductsName,Trademark,ProductsModel,CompanyName这些记录是否在表Base_State_ProductsInfo中的ProductsName,Trademark,ProductsModel,CompanyName是否存在。如果存在更新表Base_Gx_ProductsInfo中的字段Status为1否则为0,现在表Base_Gx_ProductsInfo中没有CompanyName字段,他通过customerId和Base_CustomerInfo表中customerId关联,如何关联上Base_CustomerInfo表中存在CompanyName。谢谢各位?
...全文
33 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
SQLCenter 2010-10-17
update a set a.Status = case when b.ProductsName is null then 0 else 1 end from Base_Gx_ProductsInfo a
inner join Base_CustomerInfo c on a.customerId=c.customerId
left join Base_State_ProductsInfo b on a.ProductsName=b.ProductsName and a.Trademark=b.Trademark and a.ProductsModel=b.ProductsModel and c.CompanyName=b.CompanyNam
回复
dawugui 2010-10-17
update Base_Gx_ProductsInfo set Status =
(case when exists (select 1 from (select b.ProductsName,b.Trademark,b.ProductsModel,c.CompanyName from Base_State_ProductsInfo b, Base_CustomerInfoc where b.customerId = c.customerId) m
where m.ProductsName=n.ProductsName and m.Trademark=n.Trademark and m.ProductsModel=n.ProductsModel and m.CompanyName=n.CompanyName) then 1 else 0 end)
from Base_Gx_ProductsInfo n
回复
dawugui 2010-10-17
update a set Status =
(case when exists (select 1 from (select b.ProductsName,b.Trademark,b.ProductsModel,c.CompanyName from b,c where b.customerId = c.customerId) m
where m.ProductsName=n.ProductsName and m.Trademark=n.Trademark and m.ProductsModel=n.ProductsModel and m.CompanyName=n.CompanyName) then 1 else 0 end)
from a n
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-17 04:24
社区公告
暂无公告