I have a "results" base table where I have stored data from different source tables. It's the situation where
Therefore I decieded to use a base table and have several insert...select statements to fill this table. All well (and all classic SQL). Now, I have to update rows in the results table. (Inserting rows is easy by just checking which ones are missing.) It's quite easy to find out which rows should get updated by using an EXCEPT statement. However, my idea to use INSERT...ON EXISTING UPDATE failed because the results table has its own PK (default autoincrement), which is totally unrelated to the source tables, as they are referenced by different FK columns. INSERT...ON EXISTING however daes a primary key lookup. That won't do without a fitting PK. Unlucky me. What can I do in SA 11 to solve the problem? asked 29 Jan '10, 11:08 Volker Barth |
I have used the MERGE statement for this. (It's the first time I have really used MERGE though I know about that since SA 11 was introduced. Do I have to feel ashamed:)) MERGE has the fascinating features that
I'm really impressed. A big thanks to iAnywhere:) FWIW, the statement I've used is like:
The except-part makes sure I only get matching values when source and result data are different. I guess otherwise all result data would have been updated (with identical data). - Don't know if this would perform better. As there are DEFAULT TIMESTAMP columns in resultTable, I guess thy at least would have been updated for identical data, and that was unwanted here. answered 29 Jan '10, 11:14 Volker Barth |
Wow, BANG, a do-it-yourself question and answer... cool! (a notice bar appeared WHILE I was typing my old-world answer) But... having started it, gonna finish it... You say "Inserting rows is easy by just checking which ones are missing"... that implies you know what the "natural" primary key is: the set of columns you are using to do the checking. So... make THOSE columns the actual primary key of your results table, not the "artificial" autoincrement column. Then INSERT ON EXISTING UPDATE should work. answered 29 Jan '10, 11:24 Breck Carter Breck, that is a good point (as expected!) and would generally be the easier solution. But in my particular case, the results table contains data from different source tables, and in some cases I have to check against rows with null values (i.e. the MERGE ON condition contains a "S.fk1 is null and R.fk1 is null"). So, the PK columns would contain a NULL value, which is a no-go, of course. - It might be a workaround to substitute NULL with 0, then - but to late to change the schema. |