I have a "results" base table where I have stored data from different source tables.

It's the situation where

  • a simple view would be better suited but the select is way to complex to perform well and
  • even a materialized view seems to complex to formulate (at least for me).

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%20Barth's gravatar image

Volker Barth
30.0k294447654
accept rate: 32%


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

  • the MATCH condition can be defined just on one's demand, so I could use my FK relationships for the lookup and
  • I could use the INSERT and UPDATE shorthands to specify that all column values should be updated/inserted.

I'm really impressed. A big thanks to iAnywhere:)


FWIW, the statement I've used is like:

merge into resultTable R (col_list)
using (
   select <col-list> from sourceTable
   except
   select <col-list> from resultTable) S
on R.fk1 = S.fk1 and R.fk2 = S.fk2
when matched then update
when not matched then insert;

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.

permanent link

answered 29 Jan '10, 11:14

Volker%20Barth's gravatar image

Volker Barth
30.0k294447654
accept rate: 32%

edited 09 Mar '10, 12:22

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.

permanent link

answered 29 Jan '10, 11:24

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836
accept rate: 21%

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.

(29 Jan '10, 12:07) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×28
×6

question asked: 29 Jan '10, 11:08

question was seen: 3,137 times

last updated: 09 Mar '10, 12:22