MERGE With Deletion

Kevin Wilkie shows an example of deleting data as part of a merge operation:

The last time we were together, we learned how to use the MERGE statement when we wanted to insert rows that didn’t exist and update rows that didn’t. This time we’re going to add onto that. We’re adding the seldom used, but delightfully potent – delete rows that no longer exist in the original table.

MERGE is an enticing but dangerous piece of syntax.  It looks so nice until you realize how many bugs and oddities there are in the command.

Related Posts

Odd Behavior With Altering Columns

Solomon Rutzky points out a few things which you can unintentionally change when running an ALTER TABLE [tbl] ALTER COLUMN [col] command: If the column is NOT NULL, then not specifying NOT NULL will cause it to become NULLable. The documentation for ALTER TABLE even states: ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable. Let’s see […]

Read More

LISTAGG In Snowflake DB

Koen Verbeeck continues investigating Snowflake capabilities: Since SQL Server 2017, you have the STRING_AGG function, which has almost the exact same syntax as its Snowflake counterpart. There are two minor differences:– Snowflake has an optional DISTINCT– SQL Server has a default ascending sorting. If you want another sorting, you can specify one in the WITHIN GROUP clause. […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031