SQL SERVER 下 OPENQUERY 的级联查询及更新

注意采用了ODBC方式

EXEC sp_addlinkedserver  @server = 'itgMysql', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'itgEcshop'
GO

--exec sp_dropserver 'dxmysql','droplogins'

EXEC sp_addlinkedsrvlogin  @rmtsrvname='itgMysql',@useself='false',@locallogin='sa',@rmtuser='root',
@rmtpassword='密码'




SELECT * FROM OPENQUERY (itgMysql,'select  * from ecshop.ecs_users') a
left join  zphp_logins b
on a.user_Name = b.L_Loginid
GO


update openquery(itgMysql,'select * from ecshop.ecs_users')
set  deps = b.L_UserIDlist
from zphp_logins b
where  user_name <> 'test' and  user_name <> 'abb'
and  user_name = b.L_Loginid

测试OK