Problem with ON DUPLICATE KEY UPDATE in mvc
I have the following query written in my C # class.
string query = @"INSERT INTO UserPersonalInfo(col1,col2,col3)
VALUES (@c1,@c2,@c3) ON DUPLICATE KEY UPDATE col2 = @c2, col3= @c3";
query += " SELECT SCOPE_IDENTITY()";
But when executing the query above, the following error occurs:
Incorrect syntax near the 'ON' keyword.
+3
source to share
1 answer
Use MERGE
, demo
DECLARE @ui TABLE(
col1 INT PRIMARY KEY,
col2 INT
)
INSERT INTO @ui(col1,col2)
VALUES (1,100), (2,200);
-- parameters
DECLARE @c1 INT = 1, @c2 INT = 300;
MERGE @ui AS T
USING (
VALUES (@c1, @c2)
) AS S(col1,col2)
ON (T.col1 = S.col1)
WHEN NOT MATCHED BY TARGET
THEN INSERT(col1,col2) VALUES(S.col1,S.col2)
WHEN MATCHED
THEN UPDATE SET T.col2 = S.col2;
SELECT *
FROM @ui
Assuming which col1
is PK, in your C # code, just replace string query = ...
with
string query = @"MERGE UserPersonalInfo AS T
USING (
VALUES (@c1,@c2,@c3)
) AS S(col1,col2,col3)
ON (T.col1 = S.col1)
WHEN NOT MATCHED BY TARGET
THEN INSERT(col1,col2, col3) VALUES(S.col1,S.col2, S.col3)
WHEN MATCHED
THEN UPDATE SET T.col2 = S.col2, T.col3 = S.col3";
0
source to share