27 August 2010

The MERGE Statement in SQL Server 2008

When the SQL MERGE statement was introduced in SQL Server 2008, it allowed database programmers to replace reams of messy code with something quick, simple and maintainable. The MERGE syntax just takes a bit of explaining, and Rob Sheldon is, as always, on hand to explain with plenty of examples.

Starting with SQL Server 2008, you can use a MERGE statement to modify data in a target table based on data in a source table. The statement joins the target to the source by using a column common to both tables, such as a primary key. You can then insert, modify, or delete data from the target table-all in one statement-according to how the rows match up as a result of the join.

The MERGE statement supports several clauses that facilitate the different types of data modifications. In this article, I explain each of these clauses and provide examples that demonstrate how they work. I created the examples on a local instance of SQL Server 2008. To try them out, you’ll need to first run the following script to create and populate the tables used in the examples:

As you can see, the script creates and populates the BookInventory and BookOrder tables. The BookInventory table represents the books that are or were available at a fictional book retailer. If the Quantity value for a book is 0, then the book has sold out.

The BookOrder table shows those books for which an order has been placed and delivered. If the Quantity value for a book listed in this table is 0, then the book had been requested but not included with the delivery. The Quantity values of both tables when added together represent the company’s current inventory.

NOTE: I created the BookInventory and BookOrder tables in the AdventureWorks2008 sample database. You can create the tables in any SQL Server 2008 user database. Be sure to change the USE statement in the script above to accurately reflect the target database.

Implementing the WHEN MATCHED Clause

The first MERGE clause we’ll look at is WHEN MATCHED. You should use this clause when you want to update or delete rows in the target table that match rows in the source table. Rows are considered matching when the joined column values are the same.

For example, if the BookID value in the BookInventory table matches the BookID value in the BookOrder table, the rows are considered to match, regardless of the other values in the matching rows. When rows do match, you can use the WHEN MATCHED clause to modify data in the target table. Lets look at an example to demonstrate how this works.

In the following MERGE statement, I join the BookInventory table (the target) to the BookOrder table (the source) and then use a WHEN MATCHED clause to update the Quantity column in the target table:

As you can see, the statement begins with the MERGE keyword, followed by the name of the target table. Note that the table name should be qualified as necessary. Notice that I’ve also assigned an alias (bi) to the target table to make it easier to reference that table later in the statement.

The next line in the statement is the USING clause, which is made up of the USING keyword, followed by the source table (again, qualified as necessary). I’ve also assigned an alias (bo) to this table. I then used an ON clause to join the two tables, based on the TitleID value in each table (bi.TitleID = bo.TitleID).

After I specified the target and source tables as well as the join condition, I added a WHEN MATCHED clause. The clause includes the WHEN MATCHED keywords, followed by the THEN keyword, next the UPDATE keyword, and finally a SET subclause. In this case, the SET expression specifies that the new Quantity value in the target table should equal the sum of the Quantity values from both the target and source tables (bi.Quantity = bi.Quantity + bo.Quantity). This way, the new Quantity value in the target table will reflect the accurate number of books available for sale, that is, the number of books that were on hand plus the number that arrived with the recent order.

That’s all there is to creating a basic MATCH statement. I also included a SELECT statement so we can view the contents of the target table. The following results reflect the new values in the Quantity column, as they appear in the BookInventory table after it has been updated with the MERGE statement:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

3

The Great Gatsby

0

5

Jane Eyre

5

6

Catch 22

0

8

Slaughterhouse Five

4

As the query results indicate, several of the rows have been updated to reflect the total inventory, based on the amounts in both the target and source tables. For example, the row in the BookInventory table with the TitleID value of 1 (The Catcher in the Rye) originally showed three books in stock. However, according to the BookOrder table, six more books were ordered, giving the company a total of nine books. As you would expect, the Quantity value in the BookInventory table is now 9.

You might have noticed that the book with the BookID value of 3 (The Great Gatsby) originally had a Quantity value of 0 in both the source and target tables. Suppose you want to remove from the BookInventory table any book whose Quantity value is 0 in both the target and source tables. You can easily delete such rows by adding a second WHEN MATCHED clause to your MATCH statement, as shown in the following example:

Notice that the new WHEN MATCHED clause includes a specific search condition after the AND keyword (bi.Quantity + bo.Quantity = 0). As a result, whenever rows from the two tables match and the two Quantity columns from the matched rows add up to 0, the row will be deleted, indicated by the DELETE keyword. Now the SELECT statement returns the following results:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

5

Jane Eyre

5

6

Catch 22

0

8

Slaughterhouse Five

4

As you can see, the book The Great Gatsby has been removed from the inventory. You should note, however, that a MERGE statement can include at most only two WHEN MATCHED clauses. Whenever you do include two of these clauses, the first clause must include the AND keyword, followed by a search condition, as I’ve done here. The second WHEN MATCHED clause is then applied only if the first one is not.

NOTE: The examples in this article are independent of one another. That is, for each example you run, you should first rerun the table creation script if you want your results to match the ones shown here.

Implementing the WHEN NOT MATCHED [BY TARGET] Clause

The next clause in the MERGE statement we’ll review is WHEN NOT MATCHED [BY TARGET]. (The BY TARGET keywords are optional.) You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target. For example, the BookOrder table contains a row for Gone with the Wind. However, the BookInventory table does not contain this book. The following example demonstrates how to include a WHEN NOT MATCHED clause in your MERGE statement that adds Gone with the Wind to your target table:

As the statement shows, I first specify the WHEN NOT MATCHED BY TARGET keywords, followed by the THEN keyword, and finally followed by the INSERT clause. The INSERT clause has two parts: the INSERT subclause and the VALUES subclause. You specify the target columns in the INSERT subclause and the source values in the VALUES clause. Notice that, for the VALUES subclause, I must qualify the column names with the table alias. The SELECT statement now returns the following results:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

4

Gone with the Wind

4

5

Jane Eyre

5

6

Catch 22

0

7

Age of Innocence

8

8

Slaughterhouse Five

4

Two new rows have been added to the BookInventory table: one for Gone with the Wind and one for Age of Innocence. Because the books existed in the source table, but not in the target table, they were inserted into the BookInventory table.

Implementing the WHEN NOT MATCHED BY SOURCE Clause

As you’ll recall from the discussion about the WHEN MATCHED clause, you can use that clause to delete rows from the target table. However, you can delete a row that matches a row in the source table. But suppose you want to delete a row from the target table that does not match a row in the source table.

For example, one of the rows originally inserted into the BookInventory table is for the book Catch 22. The Quantity value for that book was never updated because no order was placed for the book, that is, the book was never added to the BookOrder table. Because there are no copies of that book in stock, you might decide to delete that book from the target table. To delete a row that does not match a row in the source table, you must use the WHEN NOT MATCHED BY SOURCE clause.

NOTE: Like the WHEN MATCHED clause, you can include up to two WHEN NOT MATCHED BY SOURCE clauses in your MERGE statement. If you include two, the first clause must include the AND keyword followed by a search condition.

The following example includes a WHEN NOT MATCHED BY SOURCE clause that specifies that any rows with a quantity of 0 that do not match the source should be deleted:

After I specified the WHEN NOT MATCHED BY SOURCE keywords, I specified AND followed by a search condition (bi.Quantity = 0). I then added the THEN keyword, and next the DELETE keyword. The results returned by the SELECT statement are shown in the following table:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

4

Gone with the Wind

4

5

Jane Eyre

5

7

Age of Innocence

8

8

Slaughterhouse Five

4

As you can see, the BookInventory table no longer includes the row for Catch 22. And because the three MERGE clauses have been used together, the BookInventory now reflects the exact number of books that are currently on hand, and no books are included that are not in stock.

Implementing the OUTPUT Clause

When SQL Server 2005 was released, it included support for the OUTPUT clause in several data modification language (DML) statements. The OUTPUT clause is also available in the MERGE statement. The OUTPUT clause returns a copy of the data that you’ve inserted into or deleted from your tables. When used with a MERGE statement, the clause provides you with a powerful tool for capturing the modified data for archiving, messaging, or application purposes.

NOTE: To learn more about the OUTPUT clause, see the article “Implementing the OUTPUT Clause in SQL Server 2008” (http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/).

In the following example, I use an OUTPUT clause to pass the outputted data to a variable named @MergeOutput:

Notice that I first declare the @MergeOutput table variable. In the variable, I include a column for the action type plus three additional sets of column. Each set corresponds to the columns in the target table and includes a column that shows the deleted data and one that shows the inserted data. For example, the DelTitleID and InsTitleID columns correspond to the deleted and inserted values, respectively, in the target table.

The OUTPUT clause itself first specifies the built-in $action variable, which returns one of three nvarchar(10) values-INSERT, UPDATE, or DELETE. The variable is available only to the MERGE statement. I follow the variable with a set of column prefixes (DELETED and INSERTED) for each column in the target table. The column prefixes are followed by the name of the column they’re related to. For example, I include DELETED.TitleID and INSERTED.TitleID for the TitleID column in the target table. After I specify the column prefixes, I then include an INTO subclause, which specifies that the outputted values should be saved to the @MergeOutput variable.

After the OUTPUT clause, which is the last clause in my MERGE statement, I added a SELECT statement to retrieve the updated contents of the BookInventory table, as I’ve done in previous examples. The SELECT statement returns the following results:

TitleID

Title

Quantity

1

The Catcher in the Rye

9

2

Pride and Prejudice

3

4

Gone with the Wind

4

5

Jane Eyre

5

7

Age of Innocence

8

8

Slaughterhouse Five

4

Notice that I also include a second SELECT statement in my example above. This statement retrieves the contents of the @MergeOutput variable. The query results are shown in the following table:

ActionType

DelTitleID

InsTitleID

DelTitle

InsTitle

DelQuantity

InsQuantity

UPDATE

1

1

The Catcher in the Rye

The Catcher in the Rye

6

9

DELETE

3

NULL

The Great Gatsby

NULL

0

NULL

INSERT

NULL

4

NULL

Gone with the Wind

NULL

4

UPDATE

5

5

Jane Eyre

Jane Eyre

0

5

DELETE

6

NULL

Catch 22

NULL

0

NULL

INSERT

NULL

7

NULL

Age of Innocence

NULL

8

The results show any actions that were taken on the target table. For instance, the row for the Great Gatsby indicates that this row was deleted from the BookInventory table. The DelTitleID, DelTitle, and DelQuantity columns show the values that were deleted. However, the InsTitleID, InsTitle, and InsQuantity columns all show null values. That’s because no data was inserted into the target table for that row. If a row had been inserted, the InsTitleID, InsTitle, and InsQuantity columns will show the inserted values, but the DelTitleID, DelTitle, and DelQuantity columns will show null values because nothing is deleted when an insert is performed. Any updated rows will have values in all columns.

As you can see, when you use the OUTPUT clause in conjunction with the MERGE statement’s other three clauses-WHEN MATCHED, WHEN NOT MATCHED [BY TARGET], and WHEN NOT MATCHED BY SOURCE-you can perform multiple DML operations and verify your data modifications easily and efficiently. A MERGE statement can simplify your code, improve performance, and reduce your development effort. For more information on the MERGE statement, see the topic “Inserting, Updating, and Deleting Data by Using MERGE” in SQL Server 2008 Books Online.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 305019 times – thanks for reading.

Tags: , , , , , ,

  • Rate
    [Total: 523    Average: 4.4/5]
  • Share

Robert Sheldon

View all articles by Robert Sheldon

  • EdH334

    WHEN NOT MATCHED [BY TARGET] Clause Pitfall
    One potential pitfall with the WHEN NOT MATCHED [BY TARGET] clause is that it is treated like a RIGHT OUTER JOIN, and so you cannot add a filter to the source side, e.g.

    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED AND
    bi.Quantity + bo.Quantity = 0 THEN
    DELETE
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity
    WHEN NOT MATCHED BY TARGET AND bo.Quantity > 0
    THEN
    INSERT (TitleID, Title, Quantity)
    VALUES (bo.TitleID, bo.Title,bo.Quantity);

    will still insert records where bo.Quantity = 0.

    To see this result in a select try:

    SELECT *
    FROM BookInventory bi
    RIGHT OUTER JOIN BookOrder bo
    ON bi.TitleID = bo.TitleID
    AND bo.Quantity > 0

    not perhaps the result that you expect, but remember that the right join means “select *everything* from *BookOrder* and only those records from BookInventory where bi.TitleID = bo.TitleID and bo.Quantity > 0”. The bo.Quantity > 0 has no affect on BookOrder when BookInventory has no match.

  • Clement

    Lack of execution plan
    It would be interesting to enrich the article (very well written for a developer standpoint) with execution plans and comparison of performance and profiler statistics between the merge method and the traditional method.

    It is particularly important during an upgrade to convince developers to adopt the new Merge methodology.

  • Sean Fowler

    Examples?
    Hi; admittedly I’ve only scanned through the article but what I was looking for was a comparison of code doing it the traditional way against code using Merge.

    You say it allows us to do away with reams of messy code, but illustrating that with some of the messy code would be a useful way to demonstrate the benefit.

    I’m sure at some point I’ll read the article properly and get the point 🙂

  • Dwaine

    RE Examples?
    Sean,
    Essentially, for the equivalent of the last example, you’d have to perform an individual batch for each action.

    update bi
    set bi.qty =….
    from bi join bo

    then another pass

    delete bi
    from bi LOJ bo
    where bo.key is null

    then another…

    insert bi
    (bo columns)
    from bi ROJ bo
    where bi.key is null

    etc….

    while at the same time maintaining a table of affected rows if desired. MERGE is MUCH cleaner.

  • ChristianBahnsen

    Performance issues?
    I’m just getting started with the MERGE statement. During a SQL Lunch earlier in the week I posed the question to Andy Leonard whether the MERGE statement would be more efficient than the SSIS dataflow he was demonstrating; Andy hadn’t used MERGE. There was some discussion and some of the attendees had heard rumors that MERGE doesn’t scale well. The sentiment was MERGE might be appropriate for small operations but not for huge recordsets. What are your thoughts on this matter?

  • ChristianBahnsen

    MERGE is set-based, right?
    I was just experimenting with my first MERGE statement with a source containing about 2500 rows and a target containing about 225K rows. It took about 13 seconds to run, which is longer than I expected. That’s what prompts the question: MERGE is set-based, right?

    I assume it is.

    On reflection I realize that it has to compare source and target on every row. My code is below. Does it do the UPDATE section before the INSERT (effectively two passes) or does it do everything on one pass?

    Thanks for the timely article!

    ————————-

    MERGE tblReferrals AS T
    USING tblReferralsCopy AS S
    ON (T.OrderNumber = S.OrderNumber)
    WHEN MATCHED THEN
    UPDATE
    SET
    T.OrderingDMIS = S.OrderingDMIS,
    T.EarliestDateTime = S.EarliestDateTime,
    T.OrderingClinic = S.OrderingClinic,
    T.OrderingHCP = S.OrderingHCP,
    T.RequestedHCP = S.RequestedHCP,
    T.OutpatientClinic = S.OutpatientClinic,
    T.ClinicSpeciality = S.ClinicSpeciality,
    T.OrderingMEPR = S.OrderingMEPR,
    T.Priority = S.Priority,
    T.AncillaryProcedure = S.AncillaryProcedure,
    T.ProvisionalDx = S.ProvisionalDx,
    T.InOutPatient = S.InOutPatient,
    T.ReviewDte2 = S.ReviewDte2,
    T.Reviewer = S.Reviewer,
    T.LastStatus = S.LastStatus,
    T.AptRequestStatus = S.AptRequestStatus,
    T.WhoEnteredConsult = S.WhoEnteredConsult,
    T.DateEntered = S.DateEntered,
    T.TimeEntered = S.TimeEntered,
    T.ChangeStatus = S.ChangeStatus,
    T.MedicareStatus = S.MedicareStatus,
    T.PriorAuthStatus = S.PriorAuthStatus,
    T.TreatmentStartDate = S.TreatmentStartDate,
    T.TreatmentStopDate = S.TreatmentStopDate,
    T.NumberOfVisitsAuthorized = S.NumberOfVisitsAuthorized,
    T.ReferralNumber = S.ReferralNumber,
    T.PatIEN = S.PatIEN,
    T.Specialty = S.Specialty

    WHEN NOT MATCHED BY TARGET THEN
    INSERT (
    OrderingDMIS,
    EarliestDateTime,
    OrderNumber,
    OrderingClinic,
    OrderingHCP,
    RequestedHCP,
    OutpatientClinic,
    ClinicSpeciality,
    OrderingMEPR,
    Priority,
    AncillaryProcedure,
    ProvisionalDx,
    InOutPatient,
    ReviewDte2,
    Reviewer,
    LastStatus,
    AptRequestStatus,
    WhoEnteredConsult,
    DateEntered,
    TimeEntered,
    ChangeStatus,
    MedicareStatus,
    PriorAuthStatus,
    TreatmentStartDate,
    TreatmentStopDate,
    NumberOfVisitsAuthorized,
    ReferralNumber,
    PatIEN,
    Specialty
    )
    VALUES (
    S.OrderingDMIS,
    S.EarliestDateTime,
    S.OrderNumber,
    S.OrderingClinic,
    S.OrderingHCP,
    S.RequestedHCP,
    S.OutpatientClinic,
    S.ClinicSpeciality,
    S.OrderingMEPR,
    S.Priority,
    S.AncillaryProcedure,
    S.ProvisionalDx,
    S.InOutPatient,
    S.ReviewDte2,
    S.Reviewer,
    S.LastStatus,
    S.AptRequestStatus,
    S.WhoEnteredConsult,
    S.DateEntered,
    S.TimeEntered,
    S.ChangeStatus,
    S.MedicareStatus,
    S.PriorAuthStatus,
    S.TreatmentStartDate,
    S.TreatmentStopDate,
    S.NumberOfVisitsAuthorized,
    S.ReferralNumber,
    S.PatIEN,
    S.Specialty
    );
    SELECT * FROM tblReferrals;

  • Praze

    couple of points
    EdH334, probably the best way to filter the source is to use the clause WITH before the merge.

    Something like:

    WITH BookOrderFiltered (TitleID,Title,Quantity) AS (
    SELECT TitleID,Title,Quantity
    FROM BookOrder
    WHERE Quantity > 0
    )

    MERGE BookInventory bi
    USING BookOrderFiltered bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED AND
    bi.Quantity + bo.Quantity = 0 THEN
    DELETE
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity
    WHEN NOT MATCHED BY TARGET AND bo.Quantity > 0
    THEN
    INSERT (TitleID, Title, Quantity)
    VALUES (bo.TitleID, bo.Title,bo.Quantity);

    In terms of performance the SSIS Team posted a very good article comparing the cost of the SCD Component in SSIS vs the MERGE statement, and the bottom line was that the MERGE statement was always faster (although somethimes it might be a bit of an overkill).

    Link to the post below:

    http://blogs.msdn.com/b/mattm/archive/2010/08/05/optimizing-the-slowly-changing-dimension-wizard.aspx

  • Lakhan

    Thanks for gr8 contribution in knowledge…
    Its great to complete the insert, update, delete at the same time in two tables.

    This can be done more than two table, if yes please provide the source code as well explaination of it.

  • ChristianBahnsen

    SCDs
    I’m just starting to learn about SCDs, too. I thought that was more of a SSASOLAP concept. Can the term SCD also be applied to scenarios where one is maintaining a data warehouse mirroring OLTP?

    The code snippet I posted above will be used in a scenario where we are exporting source files from a mainframe and updating warehouse tables hourly that are on a SQL 2008 Server.

  • vikrant.a.more

    Getting error for Alias
    hi i am using SQL SERVER 2008.
    and when i run the code given in the artical.
    i am getting follwing error

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘bi’.

    can somebody please help me to get this

    i am running the code using merge

    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity;

    SELECT * FROM BookInventory;

  • vikrant.a.more

    Getting error for Alias
    hi i am using SQL SERVER 2008.
    and when i run the code given in the artical.
    i am getting follwing error

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘bi’.

    can somebody please help me to get this

    i am running the code using merge

    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity;

    SELECT * FROM BookInventory;

  • vikrant.a.more

    Getting error for Alias
    hi i am using SQL SERVER 2008.
    and when i run the code given in the artical.
    i am getting follwing error

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘bi’.

    can somebody please help me to get this

    i am running the code using merge

    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity;

    SELECT * FROM BookInventory;

  • vikrant.a.more

    Getting error for Alias
    hi i am using SQL SERVER 2008.
    and when i run the code given in the artical.
    i am getting follwing error

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘bi’.

    can somebody please help me to get this

    i am running the code using merge

    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity;

    SELECT * FROM BookInventory;

  • jatinmaestro

    How can we insert Multiple rows for non matching rows in target
    I am not it is possible or not

    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Column1]
    ,[Column2]
    ,[Column3]
    )

    VALUES
    (
    { SOURCE.[Column1]
    ,’HardCoded Value’
    ,’HardCoded Value’
    }
    ,
    {SOURCE.[Column1]
    ,’HardCoded Value’
    ,’HardCoded Value’
    }
    );

  • jatinmaestro

    How can we insert Multiple rows for non matching rows in target
    I am not it is possible or not

    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Column1]
    ,[Column2]
    ,[Column3]
    )

    VALUES
    (
    { SOURCE.[Column1]
    ,’HardCoded Value’
    ,’HardCoded Value’
    }
    ,
    {SOURCE.[Column1]
    ,’HardCoded Value’
    ,’HardCoded Value’
    }
    );

  • tech.firoz

    Second insert or update is not working on the souce
    Hi I am new for this MERGE COMMAND.

    WHEN I AM TRYING TO PERFORM THE INSERT/UPDATE ON SOURCE ON THE SAME MERGE STATEMENT GETTING ERROR.
    KINDLY HELP ME THAT I AM DOING CORRECT OR NOT OR I AM MISSING ANY CONCEPT

    MERGE BookInventory bi
    USING BookOrder bo
    ON bi.TitleID = bo.TitleID
    WHEN MATCHED AND
    bi.Quantity + bo.Quantity = 0 THEN
    DELETE
    WHEN MATCHED THEN
    UPDATE
    SET bi.Quantity = bi.Quantity + bo.Quantity
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (TitleID, Title, Quantity)
    VALUES (bo.TitleID, bo.Title,bo.Quantity)
    –WHEN NOT MATCHED BY SOURCE THEN
    –DELETE –able to perform
    WHEN NOT MATCHED BY SOURCE — but not able to perform
    AND bi.Quantity = 0 THEN
    UPDATE
    SET bo.Quantity = bi.Quantity + bo.Quantity ;

  • karti_it

    Need Help
    Could you pl help me for below issue

    MERGE into level_of_approve la
    USING
    (SELECT series_name FROM info@demo where in_use =’Y’) lb
    ON (lb.series_name = la.series_name)
    WHEN NOT MATCHED THEN
    INSERT (la.series_name)
    VALUES (lb.series_name)
    WHEN NOT MATCHED BY SOURCE THEN
    delete;

    It saying "keyword is missing" could you please help me ASAP.

    I have added AND condition also in both clause but no luck.

    Thanks in advance.
    Regards,
    Karthick

  • code monkey

    Great!!!
    Hey Thanks for taking the time to put this out there, an easy to follow step-by-step approach.

  • Zardo

    How to implement multiple joins?
    Nice article, thx.

    I don’t get one point: with UPDATE… FROM… JOIN I was able to join table A to table C over table B. Is this still possible with Merge? And how?