SQL: How to convert REPLACE INTO .... SELECT .... FROM query from MySQL to SQL server?

Question

I have a working MySQL query which updates or inserts the data (multiple rows) for a given user same as default user 'admin'. It works in MySQL but I am having hard time converting it into Microsoft SQL Server 2012 equivalent.

The MySQL query is:

REPLACE INTO table_name (user, name, sub_name, display, html_div, display_order, good_low, good_high, critical_low, warning_low, warning_high, critical_high, last_updated) 
SELECT 'user1', name, sub_name, display, html_div, display_order, good_low, good_high, critical_low, warning_low, warning_high, critical_high, last_updated 
FROM table_name WHERE user = 'admin'

What I tried to convert this into SQL Server is:

MERGE table_name AS TARGET
USING (SELECT 'user1', name, sub_name, display, html_div, display_order, good_low, good_high, critical_low, warning_low, warning_high, critical_high, last_updated FROM table_name WHERE [user] = 'admin')
    AS SOURCE ([user], name, sub_name, display, html_div, display_order, good_low, good_high, critical_low, warning_low, warning_high, critical_high, last_updated)
    ON TARGET.[user] = 'admin'
WHEN MATCHED THEN
    UPDATE
    SET [user] = 'user1', name = SOURCE.name, sub_name = SOURCE.sub_name, display = SOURCE.display, html_div = SOURCE.html_div, display_order = SOURCE.display_order, good_low = SOURCE.good_low, good_high = SOURCE.good_high, critical_low = SOURCE.critical_low, warning_low = SOURCE.warning_low, warning_high = SOURCE.warning_high, critical_high = SOURCE.critical_high, last_updated = SOURCE.last_updated
WHEN NOT MATCHED THEN
    INSERT ([user], name, sub_name, display, html_div, display_order, good_low, good_high, critical_low, warning_low, warning_high, critical_high, last_updated)
    VALUES ('user1', SOURCE.name, SOURCE.sub_name, SOURCE.display, SOURCE.html_div, SOURCE.display_order, SOURCE.good_low, SOURCE.good_high, SOURCE.critical_low, SOURCE.warning_low, SOURCE.warning_high, SOURCE.critical_high, SOURCE.last_updated);

I get error as:

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

But the query is expected to update multiple rows, so I think I need something different here.

Can you please help me to convert this MySQL query into it's equivalent SQL Server?

N West · Accepted Answer · 2016-02-04 17:33:47Z

1

MERGE requires a unique value on the source row that matches to a row in the target. Otherwise it doesn't know which one of the source rows to use - they may be different! In this case, you haven't specified any matching from TARGET to SOURCE at all. ON TARGET.[user] = 'admin' should be something like ON TARGET.[user] = SOURCE.[user]. Each source row should match one (or more target rows). But each target row should only ever match to one source row.

So you create the "lookalike" row in your SOURCE, then you compare the user id to the target table. If the user is already there, rewrite the data to be the lookalike. Otherwise insert a new row.

answered Feb 4, 2016 at 17:33

N West

6,79428 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

300 Over a year ago

Thank you for your suggestion N West. I added ON TARGET.name = SOURCE.name AND TARGET.sub_name = SOURCE.sub_name

300 Over a year ago

Instead of ON TARGET.[user] = 'admin'" Now I don't get the earlier error but a new one saying: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_comm_desk_thresholds'. Cannot insert duplicate key in object 'dbo.table_name'. The duplicate key value is (user1, acd_call_volume, ALL). The statement has been terminated. Columns user, name and sub_name are primary keys but shouldn't it update if there is a match (even if the same data exist in the row for the match) and insert if there is no match? This is what I want to happen.

300 Over a year ago

I think it's going into "NOT MATCHED" even when there is a match or I am doing something wrong here.

N West Over a year ago

Then you need to match the source and target on the entire primary key in the target table (user, name, and sub_name). Maybe you have the same name/sub name for 2 different users? Also you cannot update any of the PK fields, so pull those from your update if you have them in there.

Collectives™ on Stack Overflow

SQL: How to convert REPLACE INTO .... SELECT .... FROM query from MySQL to SQL server?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related