合并两个数据库的数据,更正部分数据

作者: 去皮哥哥 分类: sql维护,代码/源码 发布时间: 2020-06-19 15:13

select * from whmp_web_order where AgentID not in (select id from whmp_web_agents) –查询订单里面的用户已经被删除的
select * from whmp_web_order
where ReceiverPhone not in (select Phone from whmp_web_users) –根据订单信息,查找对应的新用户

–查出临时表,新用户和旧订单

select u.Name,u.ID as userid,o.OrderCode,o.ReceiverPhone,o.AgentID,o.AgentName,o.SenderID
into #temporder
from whmp_web_order as o
left join whmp_web_users as u
on o.ReceiverPhone = u.Phone

–更新临时表,新用户和新授权和旧订单

select * from #temporder
–drop table #temporder
–drop table #temporderagent
select #temporder.*,whmp_web_agents.ID as trueagentid,whmp_web_agents.AgentCode
into #temporderagent
from #temporder left join whmp_web_agents on #temporder.userid = whmp_web_agents.UserID

select * from #temporderagent

–完全找不到用户的订单,先把用户标记成-1

update #temporderagent set trueagentid = ‘1’ where trueagentid is null

–更新订单里面关联的人为新用户

update whmp_web_order set AgentID = #temporderagent.trueagentid from whmp_web_order,#temporderagent
where whmp_web_order.OrderCode = #temporderagent.OrderCode

—根据代理名字,把订单里面agentid为1的,更新一下agentid

select * from whmp_web_order where AgentID = ‘1’
select ag.ID as agid,o.* into #temp from whmp_web_order as o,whmp_web_agents as ag
where o.AgentName = ag.UserName and o.AgentID=’1′
select * from #temp

update whmp_web_order set AgentID = agid from whmp_web_order,#temp
where whmp_web_order.OrderCode = #temp.OrderCode

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注