I have two tables with identical schemae. One we'll call SourceTable and the other SHADOW.
My task is to update all the records in the SHADOW table for which their matching records in the SourceTable have a DateModified that falls within a certain period (a particular month).

The problem is that the Unique Index on these tables is a composite index made up of six fields. If it were one field, I could use a WHERE..IN construct.

I have tried concatenation of the six key fields in order to use the WHERE.. IN.. construct. For example

WHERE SHADOW.KeyField1 || SHADOW.KeyField2 || SHADOW.KeyField3...KeyField6
IN
(SELECT KeyField1 || KeyField2 || KeyField3...KeyField6
FROM SourceTable
WHERE [date clause here] etc.

But while this does work, it is exceedingly slow. Can anyone confirm that concatenation of this sort would cause very slow behavior for a large table?

Anyway, back to my question: the latest thing I tried was the following, but the error message that I get tells me that I haven't put the TEMP file into the update table's SELECT scope.

Can anyone help me do what I'm looking to do via pure SQL? I.e. without using a 4GL or the like.

Error: Table (sourcekeys) not selected in query.

SELECT * FROM sourceTable
WHERE dateModified >= '9/1/03'
AND dateModified < '10/1/03'
INTO TEMP SOURCEKEYS;

UPDATE SHADOW
SET (
field1, field2, field3...lots of fields
)
= (( SELECT
field1, field2, field3...lost of fields
FROM SOURCEKEYS
))
WHERE SHADOW.keyfield1 = SOURCEKEYS.keyfield1
AND SHADOW.keyfield2 = SOURCEKEYS.keyfield2
AND SHADOW.keyfield3 = SOURCEKEYS.keyfield3
AND SHADOW.keyfield4 = SOURCEKEYS.keyfield4
AND SHADOW.keyfield5 = SOURCEKEYS.keyfield5
AND SHADOW.keyfield6 = SOURCEKEYS.keyfield6;

Hi,

First, I suggest you that add WITH NO LOG to INTO TEMP instruction.
Second, create this into one SPL, declare foreach cursor and delete the rows.

Gustavo.

Thanks for the reply. Looks Like I may have to go with a Stored Procedure or a 4GL of some sort.

If I go for a stored procedure, it looks like I have to define local variables to hold every field that I want to update? For example, is it true that, if I have a wide table of say sixty columns, that my stored procedure will look something like

CREATE PROCEDURE myProcedure ()

DEFINE L_field1 LIKE myTable.field1;
DEFINE L_field2 LIKE myTable.field2;
DEFINE L_field3 LIKE myTable.field3;
.
.
.
.
.
.
.
.
.
...
DEFINE L_field60 LIKE myTable.field60;


FOREACH WITH HOLD SELECT field1, field2, field3, [...], ..........field60
INTO L_field1, L_field2, L_field3, [...],.......... L_field60
FROM sourceTable
WHERE dateModified >= [dateStart]
AND dateModified < [dateEnd]

BEGIN WORK
UPDATE shadowTable
SET (field1, field2, field3, [...].........field60)
= (L_field1, L_field2, L_field3, [...] ............L_field60)
WHERE field1 = L_field1
AND field2 = L_field2
AND field3 = L_field3
AND field4 = L_field4
AND field5 = L_field5
AND field6 = L_field6;
COMMIT WORK;

END FOREACH
END PROCEDURE




In other words, do I really have to define every single field that is in the table schema to do this: is there another way? can I do this very same work by using a "work area" or "temporary record buffer that looks just like the current table definition" or something, anything so that
1) I don't have to do do all that typing but more importantly...
2) I don't have to deal with what could be a maintenance nightmare (think: someone adds a column to this table)


Any ideas?
Thanks again,
-pet

Hi,

Not sure if this would work (not an Informix guru), but I've used this syntax before in other database engines:

UPDATE Shadow Sh
USING SourceTable ST
SET Sh.Field1 = ST.Field1
, Sh.Field2 = ST.Field2
..
..
WHERE Sh.Key1 = ST.Key1
AND Sh.Key2 = ST.Key2
AND Sh.Key3 = ST.Key3
AND Sh.Key4 = ST.Key4
AND Sh.Key5 = ST.Key5
AND Sh.Key6 = ST.Key6;