This is the separate DELETE
operation I had in mind:
DELETE m
FROM dbo.Mapping AS m
WHERE EXISTS
(SELECT 1 FROM @Values WHERE LeftID = m.LeftID)
AND NOT EXISTS
(SELECT 1 FROM @Values WHERE LeftID = m.LeftID AND RightID = m.RightID);
As I outline here, for a left anti-semi join, the NOT EXISTS
pattern will often outperform the LEFT JOIN / NULL
pattern (but you should always test).
Not sure if your overall goal is clarity or performance, so only you can judge if this will work out better for your requirements than the NOT MATCHED BY source
option. You'll have to look at the plans qualitatively, and the plans and/or runtime metrics quantitatively, to know for sure.
If you expect your MERGE
command to protect you from race conditions that would happen with multiple independent statements, you better make sure that is true by changing it to:
MERGE dbo.Mapping WITH (HOLDLOCK) AS target
(From Dan Guzman's blog post.)
Personally, I would do all of this without MERGE
, because there are unresolved bugs, among other reasons. And Paul White seems to recommend separate DML statements as well.
And here's why I added a schema prefix: you should always reference objects by schema, when creating, affecting, etc.
As first step I merge the two product tables. I need an full outer join on product1 and product2 tables. To obtain on outer join in mysql we can union a left join and a right join. Start with a left join. In the following query IFNULL(p1.key1, p2.key1) is useful to merge two key column coming from the two product tables and IFNULL(, 'NaN') is useful to obtain 'NaN' in the output.
select IFNULL(p1.key1, p2.key1) as key1,
IFNULL(p1.key2, p2.key2) as key2,
IFNULL(p1.serial, p2.serial) as serial,
IFNULL(p1.product_data, 'NaN') as product_data1,
IFNULL(p2.product_data, 'NaN') as product_data2
from product1 p1
left join product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;
To obtain a full outer join I need to repeat the previous query with 'right join' and union the result with the 'left join'
select IFNULL(p1.key1, p2.key1) as key1,
IFNULL(p1.key2, p2.key2) as key2,
IFNULL(p1.serial, p2.serial) as serial,
IFNULL(p1.product_data, 'NaN') as product_data1,
IFNULL(p2.product_data, 'NaN') as product_data2
from product1 p1
left join product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1,
IFNULL(p1.key2, p2.key2) as key2,
IFNULL(p1.serial, p2.serial) as serial,
IFNULL(p1.product_data, 'NaN') as product_data1,
IFNULL(p2.product_data, 'NaN') as product_data2
from product1 p1
right join product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;
For convenience, I create a view with the previous query:
create or replace view p12 as
select IFNULL(p1.key1, p2.key1) as key1,
IFNULL(p1.key2, p2.key2) as key2,
IFNULL(p1.serial, p2.serial) as serial,
IFNULL(p1.product_data, 'NaN') as product_data1,
IFNULL(p2.product_data, 'NaN') as product_data2
from product1 p1
left join product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1,
IFNULL(p1.key2, p2.key2) as key2,
IFNULL(p1.serial, p2.serial) as serial,
IFNULL(p1.product_data, 'NaN') as product_data1,
IFNULL(p2.product_data, 'NaN') as product_data2
from product1 p1
right join product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;
To check we can try with:
mysql> select * from p12 where key1 = 1 and key2 =1 ;
+------+------+--------+---------------+---------------+
| key1 | key2 | serial | product_data1 | product_data2 |
+------+------+--------+---------------+---------------+
| 1 | 1 | 0 | 15.556 | 5.556 |
| 1 | 1 | 1 | 14.996 | 4.996 |
| 1 | 1 | 2 | 12.556 | NaN |
| 1 | 1 | 3 | 15.669 | NaN |
+------+------+--------+---------------+---------------+
So the he final query is:
select mi.key1, mi.key2, p12.serial, mi.info1, mi.info2, mi.date, p12.product_data1, p12.product_data2
from main_info mi
inner join p12 on mi.key1 = p12.key1 and mi.key2 = p12.key2
order by mi.key1, mi.key2, p12.serial;
+------+------+--------+-------+-------+----------+---------------+---------------+
| key1 | key2 | serial | info1 | info2 | date | product_data1 | product_data2 |
+------+------+--------+-------+-------+----------+---------------+---------------+
| 1 | 1 | 0 | 15 | 90 | 20120501 | 15.556 | 5.556 |
| 1 | 1 | 1 | 15 | 90 | 20120501 | 14.996 | 4.996 |
| 1 | 1 | 2 | 15 | 90 | 20120501 | 12.556 | NaN |
| 1 | 1 | 3 | 15 | 90 | 20120501 | 15.669 | NaN |
| 1 | 2 | 0 | 14 | 92 | 20120601 | 12.556 | 2.556 |
| 1 | 2 | 1 | 14 | 92 | 20120601 | 13.335 | 3.335 |
| 1 | 2 | 2 | 14 | 92 | 20120601 | NaN | 2.56 |
| 1 | 2 | 3 | 14 | 92 | 20120601 | NaN | 3.556 |
| 1 | 3 | 1 | 15 | 82 | 20120801 | 12.225 | 2.225 |
| 1 | 3 | 2 | 15 | 82 | 20120801 | 13.556 | 3.556 |
| 1 | 3 | 3 | 15 | 82 | 20120801 | 14.556 | NaN |
| 2 | 1 | 0 | 17 | 90 | 20130302 | 12.556 | NaN |
| 2 | 1 | 1 | 17 | 90 | 20130302 | 13.553 | NaN |
| 2 | 1 | 2 | 17 | 90 | 20130302 | 12.335 | NaN |
| 2 | 2 | 0 | 16 | 88 | 20130601 | NaN | 2.556 |
| 2 | 2 | 1 | 16 | 88 | 20130601 | NaN | 3.553 |
+------+------+--------+-------+-------+----------+---------------+---------------+
Best Answer
Mark Sinkinson nailed it in the comments:
What you can do is adjust your predicate from this:
to the following:
This will provide a workaround that effectively equates a
NULL
in one column to aNULL
in another.So to work that into your Merge, it would be as follows: