合并时，不匹配源数据而不删除其他行。

huangapple
go评论97阅读模式
英文:

Merge when not matched by source without deleting other rows

问题

以下是您要翻译的部分：

我试图使用合并（merge）从源表更新目标表。源表中没有所有目标表的行，因此当我使用 WHEN NOT MATCHED BY SOURCE 时，所有不在我的源表中的目标行都会被删除。我只想删除特定的行。

所以，举个例子，我在我的源表中有这个：

Userid skill_id default_skill
1132 2160 1

我的目标表有很多行，但这是一个例子：

Userid skill_id default_skill
1132 421 0
1132 2160 1
1131 789 1

如果我简单地执行 WHEN NOT MATCHED BY SOURCE THEN Delete，它将删除目标表中的 1131 用户，因为它不在源表中。我只想删除目标表中与源表不匹配的那一行。所以它将删除 |1132|421|0| 行。

我应该在用户ID上执行合并，然后当匹配且技能ID不匹配时进行删除吗？但是如果我需要匹配时它们匹配并且我只想更新 default_skill 列，我不能有多个 WHEN MATCH，对吗？

这是我目前尝试执行的合并：

英文:

I am trying to update a target table from a source table using merge. The source table does not have all the rows that the target table does, so when I do WHEN NOT MATCHED BY SOURCE, all the target rows not in my source table get deleted. I only want to delete specific ones.

So for example I have this in my source table:

Userid skill_id default_skill
1132 2160 1

My target table has many rows, but here is an example:

Userid skill_id default_skill
1132 421 0
1132 2160 1
1131 789 1

If I simple do WHEN NOT MATCHED BY SOURCE THEN Delete, it will delete 1131 userid from the target table because it is not in the source. I only want to delete the one from the target table that doesn't match the source. So it would delete the |1132|421|0| row.

Would I merge on the userid then when matched and the skill_ids don't match, delete? But what if I need a when matched but they do match and I just want to update the default_skill column. I can't have multiple WHEN MATCH can I?

This is my current merge that I am trying to do it in:

  1. 	MERGE appuser_skills USING 
  2. 	(
  3. 		SELECT
  4. 			 userid,
  5. 			 #tmp.username,
  6. 			 s.value AS skill_id,
  7. 			 CASE
  8. 				WHEN s.value = #tmp.primaryskillid THEN 1
  9. 				ELSE 0
  10. 			END AS default_skill
  11. 		FROM
  12. 			#tmp
  13. 			CROSS APPLY
  14. 			dbo.splitinteger(#tmp.skilllist,',') s
  15. 			JOIN skill WITH(NOLOCK) ON skill.skill_id = s.value
  16. 	) a
  17. 	ON appuser_skills.user_id = a.userid AND appuser_skills.skill_id = a.skill_id
  18. WHEN MATCHED THEN
  19. 	UPDATE SET
  20. 		default_skill = CASE
  21. 							WHEN a.default_skill = 1 THEN 1
  22. 							ELSE 0
  23. 						END
  24. WHEN NOT MATCHED THEN
  25. 	INSERT
  26. 		(
  27. 			user_id,
  28. 			skill_id,
  29. 			weight,
  30. 			proficiency,
  31. 			default_skill
  32. 		)
  33. 	VALUES
  34. 		(
  35. 			(SELECT user_id FROM appuser WHERE appuser.username = a.username),
  36. 			a.skill_id,
  37. 			0,
  38. 			0,
  39. 			a.default_skill
  40. 		);

答案1

得分: 1

  • WHEN MATCHED AND target.default_skill <> source.default_skill THEN 条件将检查匹配的行中源表和目标表的 default_skill 值是否不匹配。在这种情况下，它会执行更新操作，将目标表中的 default_skill 值设置为与源表匹配。

  • WHEN NOT MATCHED BY SOURCE THEN DELETE 条件保持不变：它将删除目标表中没有与源表匹配的行。

英文:

  • The WHEN MATCHED AND target.default_skill <> source.default_skill THEN condition will check if the default_skill values do not match between the source and target tables for matched rows. In that case, it performs an update to set the default_skill value in the target table to match the source table.

  • The WHEN NOT MATCHED BY SOURCE THEN DELETE condition remains the same : it will delete rows from the target table that do not have a match in the source table.

  1. MERGE appuser_skills AS target
  2. USING 
  3. (
  4.     SELECT
  5.         userid,
  6.         s.value AS skill_id,
  7.         CASE
  8.             WHEN s.value = #tmp.primaryskillid THEN 1
  9.             ELSE 0
  10.         END AS default_skill
  11.     FROM
  12.         #tmp
  13.         CROSS APPLY dbo.splitinteger(#tmp.skilllist,',') s
  14.         JOIN skill WITH(NOLOCK) ON skill.skill_id = s.value
  15. ) AS source
  16. ON (target.user_id = source.userid AND target.skill_id = source.skill_id)
  17. WHEN MATCHED AND target.default_skill <> source.default_skill THEN
  18.     UPDATE SET target.default_skill = source.default_skill
  19. WHEN NOT MATCHED BY SOURCE THEN
  20.     DELETE;

答案2

得分: 1

我注意到你也想要一个插入操作。我认为不是所有的情况都可以通过合并来解决。我认为现在是时候做你从一开始就应该做的事情了，不要使用合并，或者至少使用多个语句：

  1. -- 创建测试数据
  2. drop table #source
  3. drop table #target
  4. SELECt	*
  5. into #source
  6. FROM	(
  7. 	VALUES	(1132, 2160, 1)
  8. 	,	(1132, 666, 0)
  9. ) t (Userid,skill_id,default_skill)
  11. select *
  12. into #target
  13. FROM	(
  14. 	VALUES	(1132, 421, 0)
  15. 	,	(1132, 2160, 0)
  16. 	,	(1131, 789, 1)
  17. 	) t (Userid,skill_id,default_skill)
  19. -- 实际合并操作
  20. ;WITH target AS (
  21. 	select *
  22. 	from #target t -- 我们只处理源表中的用户
  23. 	WHERE	EXISTS(
  24. 			SELECT	1
  25. 			FROM	#source s
  26. 			WHERE	s.Userid = t.UserId
  27. 	)
  28. )
  29. merge target AS t
  30. USING #source s
  31. 	ON	s.UserID = t.UserID
  32. 	AND	s.skill_id = t.skill_id
  33. WHEN NOT MATCHED BY SOURCE THEN DELETE
  34. WHEN NOT MATCHED BY TARGET THEN INSERT (UserId, skill_id, default_skill)
  35. VALUES (s.UserId, s.skill_id, s.default_skill)
  36. WHEN MATCHED and s.default_skill <> t.default_skill THEN UPDATE
  37. 	SET default_skill = s.default_skill
  38. output inserted.*, deleted.*, $action
  39. ;
  40. -- 单独插入全新用户
  41. insert into #target (
  42. 	Userid, skill_id, default_skill
  43. )
  44. select	Userid, skill_id, default_skill
  45. FROm	#source s
  46. WHERE	NOT EXISTS(
  47. 		SELECT	1
  48. 		FROM	#target t
  49. 		WHERE	t.Userid = s.userId
  50. 		--AND	t.skill_id = s.skill_id -- 不应该需要
  51. 	)
  53. select *
  54. from #source
  55. select *
  56. from #target

我选择将完全新用户的插入操作拆分出来，因为通常使用 WHERE NOT EXISTS 最容易检查。

对于删除操作，我确保参与合并的目标行只来自于 #source 表中的用户。

英文:

I noticed you also want an insert. I don't think all those can be solved by merge. I think it's time to do what you should've done from the beginning, don't use merge, or at least use several statements:

  1. -- Create test data
  2. drop table #source
  3. drop table #target
  4. SELECt	*
  5. into #source
  6. FROM	(
  7. 	VALUES	(1132, 2160, 1)
  8. 	,	(1132, 666, 0)
  9. ) t (Userid,skill_id,default_skill)
  11. select *
  12. into #target
  13. FROM	(
  14. 	VALUES	(1132, 421, 0)
  15. 	,	(1132, 2160, 0)
  16. 	,	(1131, 789, 1)
  17. 	) t (Userid,skill_id,default_skill)
  20. -- The actual merge
  21. ;WITH target AS (
  22. 	select *
  23. 	from #target t -- We work only with users in the source table
  24. 	WHERE	EXISTS(
  25. 			SELECT	1
  26. 			FROM	#source s
  27. 			WHERE	s.Userid = t.UserId
  28. 	)
  29. )
  30. merge target AS t
  31. USING #source s
  32. 	ON	s.UserID = t.UserID
  33. 	AND	s.skill_id = t.skill_id
  34. WHEN NOT MATCHED BY SOURCE THEN DELETE
  35. WHEN NOT MATCHED BY TARGET THEN INSERT (UserId, skill_id, default_skill)
  36. VALUES (s.UserId, s.skill_id, s.default_skill)
  37. WHEN MATCHED and s.default_skill <> t.default_skill THEN UPDATE
  38. 	SET default_skill = s.default_skill
  39. output inserted.*, deleted.*, $action
  40. ;
  41. -- Separate insert of completely new users
  42. insert into #target (
  43. 	Userid, skill_id, default_skill
  44. )
  45. select	Userid, skill_id, default_skill
  46. FROm	#source s
  47. WHERE	NOT EXISTS(
  48. 		SELECT	1
  49. 		FROM	#target t
  50. 		WHERE	t.Userid = s.userId
  51. 		--AND	t.skill_id = s.skill_id -- Shouldn't be needed
  52. 	)
  54. select *
  55. from #source
  56. select *
  57. from #target

I choose to split out the INSERT of completely new users, since it's usually the easiest to check using WHERE NOT EXISTS.

For the delete, i make sure target rows participating in the merge only comes from users in the #source-table.

huangapple
  • 本文由 发表于 2023年5月26日 00:25:19
  • 转载请务必保留本文链接：https://go.coder-hub.com/76334428.html
  • sql
  • sql-server
  • t-sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定