34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb.dbo.#UserRoles') is not null drop table #UserRoles
create table #UserRoles(UserId int ,RoleName varchar(50))
insert into #UserRoles select 1, 'a'
insert into #UserRoles select 1 ,'b'
insert into #UserRoles select 1 ,'c'
insert into #UserRoles select 1, 'd'
insert into #UserRoles select 2, 'e'
insert into #UserRoles select 2 ,'f'
insert into #UserRoles select 2 ,'g'
select * from #UserRoles
-- 删除旧的角色
DELETE FROM #UserRoles WHERE UserId = 1
-- 插入新的角色
insert into #UserRoles select 1, 'c'
insert into #UserRoles select 1 ,'b'
insert into #UserRoles select 1 ,'f'
select * from #UserRoles
WITH cte as (
SELECT * ,row_number() over(ORDER BY #UserRoles.RoleName) AS rn
FROM #UserRoles
WHERE UserId=1
)
UPDATE cte SET RoleName =( CASE rn WHEN 1 THEN 'c' WHEN 2 THEN 'b' WHEN 3 THEN 'f' END)
;WITH S(UserId,RoleName)
AS
(
select 1, 'c'
UNION ALL select 1 ,'b'
UNION ALL select 1 ,'f'
),T AS
(SELECT * FROM #UserRoles WHERE UserId = 1)
MERGE T
USING S ON (T.UserId=S.UserID AND T.RoleName=S.RoleName)
WHEN NOT MATCHED THEN
INSERT(UserId,RoleName)VALUES(S.UserId,S.RoleName)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;