October 29, 2018 at 9:54 am
i Guys,
Can anyone shed some light on why the code below is not working. The update will work when the records are MATCHED but the insert is not doing anything. To see the update working, change the colourid in the DEBUG to match the value in the insert statement.
BEGIN TRY
DROP TABLE #adr_test
END TRY
BEGIN CATCH
-- nothing to drop
END CATCH
CREATE TABLE #adr_test
(
style VARCHAR(5)
,size_id INT
,colour_id INT
,cost MONEY
)
INSERT INTO #adr_test (style,size_id,colour_id,cost) values ('ADR01',100,101,99.99)
SELECT * FROM #adr_test
/*DEBUG*/
DECLARE @style VARCHAR(5) = 'ADR01'
DECLARE @sizeid INT = 100
DECLARE @colourid INT = 999
DECLARE @ctncost MONEY = 1.50
/*END DEBUG*/
MERGE #adr_test AS Tgt
USING
(
SELECT
style
,size_id
,colour_id
,cost
FROM
#adr_test
WHERE
style = @style
AND
size_id = @sizeid
AND
colour_id = @colourid
) AS Src ON Src.style = Tgt.style
AND Src.size_id = Tgt.size_id
AND Src.colour_id = Tgt.colour_id
WHEN MATCHED /*AND Tgt.cost <> @ctncost */THEN
UPDATE SET Tgt.cost = @ctncost
WHEN NOT MATCHED THEN
INSERT
(
style
,size_id
,colour_id
,cost
)
VALUES
(
@style
,@sizeid
,@colourid
,@ctncost
);
SELECT * FROM #adr_test
October 29, 2018 at 10:20 am
Does this version work?BEGIN TRY
DROP TABLE #adr_test;
END TRY
BEGIN CATCH
-- nothing to drop
END CATCH;
CREATE TABLE #adr_test
(
style VARCHAR(5)
, size_id INT
, colour_id INT
, cost MONEY
);
INSERT INTO #adr_test
(
style
, size_id
, colour_id
, cost
)
VALUES
(
'ADR01', 100, 101, 99.99
);
SELECT *
FROM #adr_test;
/*DEBUG*/
DECLARE @style VARCHAR(5) = 'ADR01';
DECLARE @sizeid INT = 100;
DECLARE @colourid INT = 999;
DECLARE @ctncost MONEY = 1.50;
/*END DEBUG*/
MERGE #adr_test Tgt
USING
(
SELECT
style = @style
, size_id = @sizeid
, colour_id = @colourid
, cost = @ctncost
) Src
ON Src.style = Tgt.style
AND Src.size_id = Tgt.size_id
AND Src.colour_id = Tgt.colour_id
WHEN MATCHED /*AND Tgt.cost <> @ctncost */ THEN
UPDATE SET Tgt.cost = @ctncost
WHEN NOT MATCHED THEN
INSERT
(
style
, size_id
, colour_id
, cost
)
VALUES
(
@style, @sizeid, @colourid, @ctncost
);
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply