CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Kinexus
Kinexus

Reputation: 12904

SQL Merge statement not working in Stored Procedure

The following code does not seem to work. If the address does not exist, it does not insert the new record. However, if the address does exist, it does get updated.

ALTER PROCEDURE [Users].[UpdateAddress]
    @UserId int,
    @Address1 varchar(100),
    @Address2 varchar(100),
    @Town varchar(100),
    @County varchar(50),
    @PostCode varchar(50),
    @Country varchar(50),
    @Type INT
AS

MERGE [Users].[Addresses] AS Target
USING (SELECT UserId FROM [Users].[Addresses] WHERE UserId = @UserId) AS Source
ON (Source.UserId = Target.UserId)

WHEN MATCHED THEN
    UPDATE SET Target.Address1 = @Address1,
    Target.Address2 = @Address2,
    Target.Town = @Town,
    Target.County = @County,
    Target.Postcode = @Postcode,
    Target.Country = @Country
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([UserId], [Address1], [Address2], [Town], [County], [PostCode], [Country], [Modified], [Type])
    VALUES(@UserId, @Address1, @Address2, @Town, @County, @PostCode, @Country, GetDate(), @Type);

Upvotes: 5

Views: 11845

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Your Source shouldn't rely upon the target table. Try instead:

MERGE [Users].[Addresses] AS Target
USING (select @UserID,@Address1,@Address2,@Town,@County,@PostCode,@Country,@Type)
    AS Source (UserID,Address1,Address2,Town,County,PostCode,Country,Type)
ON (Source.UserId = Target.UserId)
WHEN MATCHED THEN
    UPDATE SET Target.Address1 = Source.Address1,
    Target.Address2 = Source.Address2,
    Target.Town = Source.Town,
    Target.County = Source.County,
    Target.Postcode = Source.Postcode,
    Target.Country = Source.Country
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([UserId], [Address1], [Address2], [Town], [County], [PostCode], [Country], [Modified], [Type])
    VALUES(Source.UserId, Source.Address1, Source.Address2, Source.Town, Source.County, Source.PostCode, Source.Country, GetDate(), Source.Type);

At the moment, you're creating a zero-row Source rowset, so of course nothing happens in the merge.

Upvotes: 16

Related Questions