Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: Help needed with an UPDATE statement.

    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;

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Help needed with an UPDATE statement.

    Originally posted by HeavyPet
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    5
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    6

    Re: Help needed with an UPDATE statement.

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •