On 06/08/10 04:39, Boxuan Zhai wrote:
I have seen a lively discussion about the DO NOTING action in MERGE command.
And, I think most people want it. So it will be added to my next patch.
Before the implementation, I still have some questions to confirm:
1. If we have a DO NOTHING action specified, it should be the last WHEN
clause. It must be of the NOT MATCHED cases, and it CANNOT have any
additional action qualifications. Am I correct?
It would be useful to specify it in WHEN MATCHED sometimes, and not
necessarily the last. For example:
MERGE INTO Stock S
USING DailySales DS ON S.Item = DS.Item
WHEN MATCHED AND (QtyOnHand ‐ QtySold = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET QtyOnHand = QtyOnHand ‐ QtySold
-- Don't add new inactive items to stock if not there already
WHEN MATCHED AND (itemtype = 'inactive') THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES (Item, QtySold);
It shouldn't be difficult to support DO NOTHING in all cases, right?
2. If no DO NOTHING specified, we will imply a INSERT DEFAULT VALUES action
as the end of MERGE.
My question is, is this action taken only for the NOT MATCHED tuples? If
this is the case, then what about the MATCHED tuples that match not previous
actions? Ignore them?
That means we are in fact going to add two implicit WHEN clause:
a) WHEN NOT MATCHED INSERT default values;
b) WHEN MATCHED THEN DO NOTHING.
OR, is the INSERT DEFAULT VALUES applied to ALL tuples not matter they are
MATCHED or not?
We'll need to figure out what the SQL standard says about this. I tried
reading the spec but couldn't readily understand what the default action
should be. Does someone else know that? What do other DBMSs do?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
