UPDATE与多表关联更新

SQL环境：SQL Server 2008

业务需求：将查询的记录集合更新部分字段，涉及到多张表

1.SQL查询获得的记录集合：

SELECT u.USER_NAME_,g.desc_, u.email_, u.mobile_
FROM BDF2_GROUP g, BDF2_GROUP_MEMBER gm, UBP_USER u
where g.id_=gm.group_id_ and gm.username_=u.user_name_
AND g.desc_ IN (
		'310115-02', 
		'310118-02', 
		'310112-02'
	)
	
	
order by u.USER_NAME_

2.将原有SQL转化成等价的SQL【采用SQL子查询】

SELECT USER_NAME_, email_, mobile_
FROM UBP_USER
WHERE USER_NAME_ IN (
	SELECT username_
	FROM BDF2_GROUP_MEMBER
	WHERE group_id_ IN (
		SELECT id_
		FROM BDF2_GROUP
		WHERE desc_ IN (
			'310115-02', 
			'310118-02', 
			'310112-02'
		)
	)
)
ORDER BY USER_NAME_

3.SQL修改一张表部分字段【查询时涉及多张表】

UPDATE UBP_USER
SET email_ = 'abcd@qq.com', mobile_ = '11111111111'
WHERE USER_NAME_ IN (
		SELECT username_
		FROM BDF2_GROUP_MEMBER
		WHERE group_id_ IN (
			SELECT id_
			FROM BDF2_GROUP
			WHERE desc_ IN (
				'310115-02', 
				'310118-02', 
				'310112-02'
			)
		)
	)