SEO

I loaded a flat file into an SQL table and verified the data. Now I want
to move the data from the input table to the real table. I thought to
use a SQL statement like:

update is2.animals t1 join nullid.nalfherd_in t2
on t1.prefix = t2.reg_prefix
and t1.regnum = t2.regisno
set t1.color=t2.color
;

where t1 is the real table and t2 it the input table. I know I can
achieve this in an SQL procedure, but I was looking for an easier (and
probably more efficient) way.

I also think updating a view which joins the tables will work, but that
gets keyboard intensive, especially for me, with only one hand.

The real problem has 8 tables to be updated from 3 input tables with
about 500 columns between them.

> update is2.animals t1 join nullid.nalfherd_in t2
> on t1.prefix = t2.reg_prefix
> and t1.regnum = t2.regisno
> set t1.color=t2.color
> ;
Interesting syntax, not supported by any DBMS I know of ;-)

MERGE INTO is2.animals t1
USING nullid.nalfherd_in t2
ON on t1.prefix = t2.reg_prefix
and t1.regnum = t2.regisno
WHEN MATCHED THEN UPDATE SET t1.color=t2.color;

Preferably you shoudl be on V8.1 FP5 since there have been numerous
performance improvements for MERGE.

For older releases:
UPDATE is2.animals t1
SET t1.color = (SELECT t2.color FROM nullid.nalfherd_in t2
WHERE t1.prefix = t2.reg_prefix
and t1.regnum = t2.regisno)
WHERE EXISTS(SELECT 1 FROM nullid.nalfherd_in t2
WHERE t1.prefix = t2.reg_prefix
and t1.regnum = t2.regisno);

> The real problem has 8 tables to be updated from 3 input tables with
> about 500 columns between them.
Interesting flow. This is a fact table with 7 dimension tables?
If you coudl post a more elaborate example that woudl be helpful.
In general DB2 does not support update through JOIN only through UNION ALL.
You may be able to use SELECT FROM UPDATE and pipeline the updates in a
common table expression:
WITH u1 AS (SELECT ... FROM NEW TABLE(UPDATE T1 INCLUDE (...) SET ....)
u2 AS (SELECT ... FROM NEW TABLE (UPDATE T2 INCLUDE (...) SET ...
= (FROM y1 ...)...
....



--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:
>
>> update is2.animals t1 join nullid.nalfherd_in t2
>> on t1.prefix = t2.reg_prefix
>> and t1.regnum = t2.regisno
>> set t1.color=t2.color
>> ;
>
> Interesting syntax, not supported by any DBMS I know of ;-)
>
> MERGE INTO is2.animals t1
> USING nullid.nalfherd_in t2
> ON on t1.prefix = t2.reg_prefix
> and t1.regnum = t2.regisno
> WHEN MATCHED THEN UPDATE SET t1.color=t2.color;
>
> Preferably you shoudl be on V8.1 FP5 since there have been numerous
> performance improvements for MERGE.
>
> For older releases:
> UPDATE is2.animals t1
> SET t1.color = (SELECT t2.color FROM nullid.nalfherd_in t2
> WHERE t1.prefix = t2.reg_prefix
> and t1.regnum = t2.regisno)
> WHERE EXISTS(SELECT 1 FROM nullid.nalfherd_in t2
> WHERE t1.prefix = t2.reg_prefix
> and t1.regnum = t2.regisno);
>
>> The real problem has 8 tables to be updated from 3 input tables with
>> about 500 columns between them.
>
> Interesting flow. This is a fact table with 7 dimension tables?
> If you coudl post a more elaborate example that woudl be helpful.
> In general DB2 does not support update through JOIN only through UNION ALL.
> You may be able to use SELECT FROM UPDATE and pipeline the updates in a
> common table expression:
> WITH u1 AS (SELECT ... FROM NEW TABLE(UPDATE T1 INCLUDE (...) SET ....)
> u2 AS (SELECT ... FROM NEW TABLE (UPDATE T2 INCLUDE (...) SET ...
> = (FROM y1 ...)...
> ...
>
>
>

I knew it was invalid, it was, in some sense, illustrative of what I
wanted to do. I also really do know how to spell heLp, evidence to the
contrary.

Thanks for the pointers. The second looks slower than molasses uphill in
February unless the optimizer is almost telepathic in figuring out what
I mean from what I say. I am running 8.1.5 so the MERGE solution should
work for me.

Where did you find the documentation for the MERGE command? I have d/l'd
the _SQL Reference Volume 2_ from the IBM site and it doesn't show MERGE
as a statement (it goes from 'LOCK TABLE' straight to 'OPEN').

The real problem is I am getting the updates/addition to my real, almost
correctly designed (IMNSHO :-), database from several sources which use
different views of the same data. The data pertains to animals, and I
have separate tables for things like birth data, weaning data, genetic
fitness data, etc., since we almost never know all of the data for one
animal, or it doesn't pertain to a particular animal. The saving in
space/retrieval time is significant because the ultimate goal is to have
10^8 animals in the database. Unfortunately, my data suppliers see the
data differently, so there could be anywhere from 1 (massive) table
which would update each of my tables to a table set which is isomorphic
to my table set modulo column order. Since my data suppliers cannot
decide on a common unique identifier, I am using a generated field for
the identification of each animal and mapping the different suppliers
(not necessarily UNIQUE, especially over time) identifier to it. I do
this in an SQL procedure which seems to run forever. all of the
"dependent" tables use this same key. To make the problem even more
interesting each supplier presents pedigree data differently, some times
causing as many as 15 animals to be added to the main table for each
input record!

Which leads to another question: can I use the MERGE statement to update
a column which IS NOT NULL, or does it operate similarly to the COALESCE
function? From the use of SET in the statement, it would appear that
updates are possible. Also like UPDATE's SET operand, there can be
multiple attributes set in one statement, right?