Updating joined tables in sql server


25-Jul-2017 16:37

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.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.Be sure to change the USE statement in the script above to accurately reflect the target database.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.

As you would expect, the Quantity value in the Book Inventory table is now 9.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.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. Now the SELECT statement returns the following results: 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.The following results reflect the new values in the Quantity column, as they appear in the Book Inventory table after it has been updated with the MERGE statement: 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.