Joe Celko s SQL for Smarties - Advanced SQL Programming P27. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 232 CHAPTER 8 TABLE OPERATIONS CREATE TABLE Foo col_a CHAR 1 NOT NULL col_b INTEGER NOT NULL INSERT INTO Foo VALUES A 0 B 0 C 0 CREATE TABLE Bar col_a CHAR 1 NOT NULL col_b INTEGER NOT NULL INSERT INTO Bar VALUES A 1 A 2 B 1 C 1 You run this proprietary UPDATE with a FROM clause UPDATE Foo SET FROM Foo INNER JOIN Bar ON The result of the UPDATE cannot be determined. The value of the column will depend upon either order of insertion if there are no clustered indexes present or on order of clustering but only if the cluster is not fragmented . MERGE Statement SQL-99 added a single statement to mimic a common magnetic tape file system merge and insert procedure. The business logic in a pseudocode is like this. FOR EACH row IN the Transactions table DO IF working row NOT IN Master table THEN INSERT working row INTO the Master table ELSE UPDATE Master table SET Master table columns to the Transactions table values WHERE they meet a matching criteria END IF END FOR MERGE Statement 233 In the 1950s we would sort the transaction tape s and Master tape on the same key read each one looking for a match then perform whatever logic is needed. In its simplest form the merge statement looks like this MERGE INTO table name AS correlation name USING table reference ON search condition WHEN NOT MATCHED AND search condition THEN modification operation . ELSE IGNORE You will notice that use of a correlation name in the merge into clause is in complete violation of the principle that a correlation name effectively creates a temporary table. There are several other places where SQL 2003 destroyed the original SQL language model but you do not have to write irregular syntax in all cases. After a row is matched or not to the target table you can add more search condition s in the WHEN clauses. The modification operation clause can include insertion update or delete operations that follow the same rules as those single statements. This .
