Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Exclamation Unanswered: Updating Table Columns !!

    HI
    Need Update syntax for the following scenario in DB2...

    Have two tables
    schema1.A ( COL1 ,COL2) and schema2.B (COL1, COL2 ). Both Tables have same structure but in different schema.

    Have to update A.COL1 values equal to B.COL1
    where A.COL2=B.COL2
    and A.COL1!=B.COL1

    ie to update schema.A(COL1 ) values for all the same COL2 values in both the tables having different COL1 values in schema.A tables.

    Not sure of the DB2 sytax.
    Possible in Oracle

    Appreciate your help ASAP.

    Thanks
    Bollin99

  2. #2
    Join Date
    May 2004
    Location
    USA
    Posts
    1

    Thumbs up Update DB2 Syntax

    Here you go......

    CREATE TABLE TEST1.TABA (TYPE CHAR(5), AMOUNT INTEGER);

    CREATE TABLE TEST2.TABB (TYPE CHAR(5), AMOUNT INTEGER);

    INSERT INTO TEST1.TABA VALUES('AAA', 11);
    INSERT INTO TEST1.TABA VALUES('BBB',22);
    INSERT INTO TEST1.TABA VALUES('CCC',33);

    INSERT INTO TEST2.TABB VALUES('AAA', 10);
    INSERT INTO TEST2.TABB VALUES('BBB',20);

    SELECT * FROM TEST1.TABA;

    SELECT * FROM TEST2.TABB;

    UPDATE TEST1.TABA SET AMOUNT =
    (SELECT AMOUNT FROM TEST2.TABB B
    WHERE TEST1.TABA.TYPE = B.TYPE)
    WHERE TEST1.TABA.TYPE IN (SELECT TYPE FROM TEST2.TABB);

    or

    UPDATE Schema1.A SET COL1 =
    (SELECT COL1 FROM Schema2.B B
    WHERE Schema1.A.COL2 = B.COL2)
    WHERE Schema1.A.COL2 IN (SELECT COL2 FROM Schema2.B)

Posting Permissions

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