合并声明问题 [英] Merge Statement Issue

问题描述

大家好，
我遇到的Merge语句有一个问题，以下是我的merge语句，它的插入和更新语法运行良好，但删除不起作用.有人可以建议我需要修改的内容吗?

Hi All,
I am facing a issue with Merge statement below is my merge statement and its insert and update syntax is running fine but delete is not working. Can anybody suggest me what need to modify

string Sql_Statement = @" MERGE INTO Organization With (ROWLOCK) AS Target " +
                             " USING (VALUES " + valuesCommunities.Substring(0, valuesCommunities.Length - 1) + " ) " +
                             " AS Source (LegacyOrganizationID, CustomerID, OrganizationName, Address1, Address2, City, [State], CountryID, Zip, Phone, Fax, DateAdded, AddedBy, DateModified, ModifiedBy, DateDeleted, DeletedBy, IsActive, ReferenceID) " +
                             " ON Target.legacyOrganizationId=Source.legacyOrganizationID " +
                             " AND Target.CustomerId=Source.CustomerID " +
                             " WHEN MATCHED THEN " +
                             " UPDATE SET LegacyOrganizationID = Source.LegacyOrganizationID, CustomerID = Source.CustomerID, OrganizationName = Source.OrganizationName, Address1 = Source.Address1, Address2 = Source.Address2, City = Source.City, State = Source.State, CountryID = Source.CountryID, Zip = Source.Zip, Phone = Source.Phone, Fax = Source.Fax,  DateModified = Source.DateModified, ModifiedBy = Source.ModifiedBy, DateDeleted = Source.DateDeleted, DeletedBy = Source.DeletedBy, IsActive = Source.IsActive" +
                             " WHEN NOT MATCHED BY TARGET THEN " +
                             " INSERT (LegacyOrganizationID, CustomerID, OrganizationName, Address1, Address2, City, [State], CountryID, Zip, Phone, Fax, DateAdded, AddedBy, DateModified, ModifiedBy, DateDeleted, DeletedBy, IsActive) " +
                             " VALUES (Source.LegacyOrganizationID, Source.CustomerID, Source.OrganizationName, Source.Address1, Source.Address2, Source.City, Source.[State], Source.CountryID, Source.Zip, Source.Phone, Source.Fax, Source.DateAdded, Source.AddedBy, Source.DateModified, Source.ModifiedBy, Source.DateDeleted, Source.DeletedBy, Source.IsActive) " +
                             " WHEN NOT MATCHED BY Source THEN Delete" +
                             " OUTPUT $action, Inserted.OrganizationID, Inserted.LegacyOrganizationID,Source.ReferenceID; ";

等待您的建议

谢谢
AP

Waiting for your kind sugggestion

Thanks
AP

推荐答案

操作，Inserted.OrganizationID，Inserted.LegacyOrganizationID，Source.ReferenceID； ";

action, Inserted.OrganizationID, Inserted.LegacyOrganizationID,Source.ReferenceID; ";

等待您的建议

谢谢
AP

Waiting for your kind sugggestion

Thanks
AP

该语句应该可以正常工作.语法正确，您那里没有重复的语句，并且由于没有编译或运行时错误，因此您知道该查询已被SQL接受.我的建议是做一些实验(如果您还没有的话).在目的地中创建一个行，您肯定不会在源中创建行.确保两个ID绝对不在您的来源中，然后重试.也许是因为您的匹配标准太笼统，您才获得了意想不到的匹配.

That statement should work just fine. The syntax is correct, you don''t have a duplicate statement in there, and since you don''t get a compile or runtime error you know the query is being accepted by SQL. My suggestion would be to do some experiments (if you haven''t already). Create a row in your destination that you know for sure wouldn''t be in your source. Make sure that the two IDs are definitely not in your source and then try it again. Maybe you are getting a match that you didn''t expect because your match criteria is too generic.

这篇关于合并声明问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！