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

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)