Stack Overflow
Stack Overflow

Questions tagged [sql-merge]

`SQL MERGE` statement allows us to insert new rows into table and update existing rows depending on given condition. Use this tag in addition to [tag:sql] to make question better categorized.

SQL MERGE statement allows us to insert new rows into table and update existing rows depending on given condition.

Examples of using MERGE statement:

Use this tag in addition to to make question better categorized.

324 questions
390
votes
7 answers

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT ... ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation. Given that PostgreSQL doesn't support it directly (before pg 9.5), how…
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
159
votes
8 answers

ORA-30926: unable to get a stable set of rows in the source tables

I am getting ORA-30926: unable to get a stable set of rows in the source tables in the following query: MERGE INTO table_1 a USING (SELECT a.ROWID row_id, 'Y' FROM table_1 a ,table_2 b ,table_3 c WHERE…
Omnipresent
  • 29,434
  • 47
  • 142
  • 186
36
votes
3 answers

SQL merge not matched by target vs not matched by source

What is the difference between NOT MATCHED BY SOURCE vs NOT MATCHED BY TARGET? For example: Does WHEN NOT MATCHED BY SOURCE mean that the records exist in the target but not in the source? - so we can delete them ? and WHEN NOT MATCHED BY TARGET -…
TResponse
  • 3,940
  • 7
  • 43
  • 63
26
votes
6 answers

Pipes and filters at DBMS-level: Splitting the MERGE output stream

Scenario We have a pretty standard data import process in which we load a staging table, then MERGE it into a target table. New requirements (green) involve capturing a subset of the imported data into a separate queue table for completely unrelated…
tne
  • 7,071
  • 2
  • 45
  • 68
22
votes
1 answer

UPDATE-no-op in SQL MERGE statement

I have a table with some persistent data in it. Now when I query it, I also have a pretty complex CTE which computes the values required for the result and I need to insert missing rows into the persistent table. In the end I want to select the…
Lucero
  • 59,176
  • 9
  • 122
  • 152
16
votes
3 answers

Oracle MERGE statement in H2 database

We started to use the H2 in memory database for automated testing. We use Oracle for our production & dev environments. So the idea is to duplicate the table structure in H2 test-database as it is in our Oracle dev-database. The Oracle SQL…
user1877775
  • 201
  • 1
  • 2
  • 4
16
votes
4 answers

When doing a MERGE in Oracle SQL, how can I update rows that aren't matched in the SOURCE?

I have a main database and a report database, and I need to sync a table from main into report. However, when an item gets deleted in the main database, I only want to set an IsDeleted flag in the report database. What is an elegant way to do…
Scott Rippey
  • 15,614
  • 5
  • 70
  • 85
15
votes
4 answers

SQL MERGE to remote (linked) server table

Is it possible to utilize the SQL MERGE function on a linked server's database table? The end goal is to synchronize the remote table with our local SQL server table. I’ve done some research online and couldn’t find any related information. If it is…
devHead
  • 794
  • 1
  • 15
  • 38
13
votes
2 answers

UPDATE or MERGE of very big tables in SQL Server

I need to perform a daily update of a very large (300M records) and broad TABLE1. The the source data for the updates is located in another table UTABLE that is 10%-25% the rows of TABLE1 but is narrow. Both tables have record_id as a primary…
Sergio Kozlov
  • 535
  • 1
  • 4
  • 13
13
votes
1 answer

MERGE in Entity Framework

Is there a way to call T-Sql's MERGE command from .NET Entity framework 4?
alerya
  • 3,125
  • 3
  • 28
  • 50
12
votes
1 answer

MERGE vs. UPSERT

I have an application I’m writing in access with a SQL server backend. One of the most heavily used parts is where the users selects an answer to a question, a stored procedure is then fired which sees if an answer has already been given, if it has…
Kevin Ross
  • 7,185
  • 2
  • 21
  • 27
11
votes
2 answers

Oracle Merge vs Select then Insert or Update

What is faster? the Merge statement MERGE INTO table_name USING dual ON (row_id = 'some_id') WHEN MATCHED THEN UPDATE SET col_name = 'some_val' WHEN NOT MATCHED THEN INSERT (row_id, col_name) VALUES ('some_id',…
DRTauli
  • 731
  • 1
  • 8
  • 25
11
votes
1 answer

SQL Server MERGE + Joining other tables

I am using the MERGE statement within a database project to populate reference data from a static value set, such as the following below: MERGE INTO dbo.[User] AS TARGET USING (VALUES ('me@somewhere.com', 'My Name')) AS SOURCE(UserName,…
Rich
  • 2,076
  • 1
  • 15
  • 16
10
votes
2 answers

SQL Merge with inserting into the third table

I want to create a merge that will compare two tables and insert not matched values into another third table or table variable something like this: MERGE Assets AS target USING (@id, @name)FROM Sales AS source (id, name) ON (target.id =…
iLemming
  • 34,477
  • 60
  • 195
  • 309
10
votes
0 answers

Why does a merge into a temporal table with a nonclustered index in the history table throw an error

I get the following error when I try to merge under a few conditions. Is someone able to explain why? Seems like it's a problem with SQL Server itself, but I wanted to post it here to confirm. Attempting to set a non-NULL-able column's value to…
CSharpFiasco
  • 204
  • 3
  • 8
1
2 3
…
21 22