![]() ![]() VALUES (u.CustomerID, u.FirstName, u.MiddleName, u.LastName, u.DateOfBirth) INSERT (CustomerID, FirstName, MiddleName, LastName, DateOfBirth) USING #Updates u ON u.CustomerID = c.CustomerID To go from that to an update or a merge statement, is fairly simple… Update UPDATE c SELECT u.FirstName, u.MiddleName, u.LastName, u.DateOfBirthĬool right? This is giving you all records in #Customer which do not have a matching record in #Updates. SELECT c.FirstName, c.MiddleName, c.LastName, c.DateOfBirth Let’s use EXISTS and EXCEPT to find all records which changed… SELECT * Now we have a copy of the #Customer table named #Updates, and we’ve made a few changes to the data. INSERT INTO #Updates (CustomerID, FirstName, MiddleName, LastName, DateOfBirth) UPDATE #Updates SET DateOfBirth = '' WHERE CustomerID = 2 - Change DateOfBirth UPDATE #Updates SET MiddleName = NULL WHERE CustomerID = 3 - Remove Middle Name UPDATE #Updates SET MiddleName = 'John' WHERE CustomerID = 9 - Add Middle Name UPDATE #Updates SET LastName = 'Brown' WHERE CustomerID = 5 - Change Last Name SELECT c.CustomerID, c.FirstName, c.MiddleName, c.LastName, c.DateOfBirth Now lets create a new table where we can make modifications to the data for us to sync back to the original #Customer table: IF OBJECT_ID('tempdb.#Updates','U') IS NOT NULL DROP TABLE #Updates -SELECT * FROM #Updates Note that MiddleName and DateOfBirth allow NULL. Here we’ve got some sample data…We have a customer table, where we store the customers first, middle and last name, and their birth date. VALUES ( 1, 'Sheldon', 'Dennis' ,'Saunders', '') INSERT INTO #Customer (CustomerID, FirstName, MiddleName, LastName, DateOfBirth) Let’s set up some sample data: IF OBJECT_ID('tempdb.#Customer','U') IS NOT NULL DROP TABLE #Customer -SELECT * FROM #Customer It’s this difference that helps us use it to find changed rows. The other thing to note is that the EXCEPT operator treats the comparison of NULL values as equal. The first example returns nothing because the two sets match, but the following two examples return records from the first set because it was unable to find any matching records in the second set. The most basic examples would be: - Returns nothing The EXCEPT set operator compares two sets of records, and returns all of the records from the first set that don’t have a matching record in the second set. This is where my favorite trick comes in Using the EXISTS operator and the EXCEPT set operator to identify changed rows. What happens when LastName is changed to allow NULL? The update is no longer correct, and needs to be fixed. This works…but it is hard to read, and now you need to keep track of which columns are nullable and which ones aren’t. WHEN c.DateOfBirth IS NULL AND u.DateOfBirth IS NULL THEN 0 OR CASE WHEN c.DateOfBirth = u.DateOfBirth THEN 0 WHEN c.MiddleName IS NULL AND u.MiddleName IS NULL THEN 0 OR CASE WHEN c.MiddleName = u.MiddleName THEN 0 You could do something like this… UPDATE c But what if MiddleName and DateOfBirth allows NULLs? This works fine, as long as every column isn’t nullable. JOIN #Updates u ON u.CustomerID = c.CustomerID One method is to compare each column in the WHERE clause separating each comparison with an OR… UPDATE c ![]() Of course, there’s always more than one way to bake a cake. One of the daunting parts of writing updates, especially with a large number of columns, is figuring out which records actually changed, and only updating those records. select * from a FULL OUTER JOIN b on a.a = b.Maybe you’re building an ETL process, like loading a file, or need to compare two tables? How would you write that update? If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa. Select a.*, b.* from a,b where a.a(+) = b.b Ī full outer join will give you the union of A and B, i.e. select * from a RIGHT OUTER JOIN b on a.a = b.b Select a.*, b.* from a,b where a.a = b.b(+) Ī right outer join will give all rows in B, plus any common rows in A. select * from a LEFT OUTER JOIN b on a.a = b.b Select a.*, b.* from a,b where a.a = b.b Ī left outer join will give all rows in A, plus any common rows in B. select * from a INNER JOIN b on a.a = b.b Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.Īn inner join using either of the equivalent queries gives the intersection of the two tables, i.e. Suppose you have two tables, with a single column each, and data as follows: A B the inner part of a Venn diagram intersection.Īn outer join of A and B gives the results of A union B, i.e. Assuming you're joining on columns with no duplicates, which is a very common case:Īn inner join of A and B gives the result of A intersect B, i.e. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |