I have two tables and I want to update a field from one table to another.

Table: Customer Table: Address
Column:cust_id Column: cust_id
active_addr_id addr_id

What update statement can I use to update all of the active_addr_id in customer table with teh addr_id in the Address table where cust_id is equal ?

Thanks

I forgot, I am using DB2 version 8.0 on Linux.

Check this statement :

UPDATE CUSTOMER C SET ACTIVE_ADDR_ID=(select addr_id from address a where c.cust_id=a.cust_id)

Cheers
Sathyaram

Schema : CUSTOMER
Table : CUSTOMER
Column : CUSTOMER_ID
ACTIVE_ADDRESS_ID

Schema : CUSTOMER
Table : ADDRESS
Column : CUSTOMER_ID
ADDRESS_ID

Here is what I ran:
UPDATE CUSTOMER.CUSTOMER C
SET ACTIVE_ADDRESS_ID =
(SELECT
ADDRESS_ID
FROM
CUSTOMER.ADDRESS A
WHERE
C.CUSTOMER_ID = A.CUSTOMER_ID);

Here is what I received back:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row. SQLSTATE=21000

Your address table has multiple records for the same customer ... you may have to identify which record's addr_id the customer table should have .... Logically, this makes sense to me ..

The following should be able to illustrate the point technically ...

DELETE FROM CUSTOMER

DB20000I The SQL command completed successfully.



DELETE FROM ADDRESS

DB20000I The SQL command completed successfully.



INSERT INTO CUSTOMER VALUES(1,'01'),(2,'02')

DB20000I The SQL command completed successfully.



INSERT INTO ADDRESS VALUES(1,'09'),(2,'19')

DB20000I The SQL command completed successfully.



select * from customer c



CUST_ID ACTIVE_ADDR_ID

----------- --------------

1 01

2 02



2 record(s) selected.





select * from address



CUST_ID ADDR_ID

----------- -------

1 09

2 19



2 record(s) selected.





UPDATE CUSTOMER C SET ACTIVE_ADDR_ID=(select addr_id from address a where c.cust_id=a.cust_id)

DB20000I The SQL command completed successfully.



select * from customer c



CUST_ID ACTIVE_ADDR_ID

----------- --------------

1 09

2 19



2 record(s) selected.





select * from address



CUST_ID ADDR_ID

----------- -------

1 09

2 19



2 record(s) selected.





INSERT INTO ADDRESS VALUES(2,'15')

DB20000I The SQL command completed successfully.



select * from customer c



CUST_ID ACTIVE_ADDR_ID

----------- --------------

1 09

2 19



2 record(s) selected.





select * from address



CUST_ID ADDR_ID

----------- -------

1 09

2 19

2 15



3 record(s) selected.





UPDATE CUSTOMER C SET ACTIVE_ADDR_ID=(select addr_id from address a where c.cust_id=a.cust_id)

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES

INTO statement is more than one row. SQLSTATE=21000



TERMINATE

DB20000I The TERMINATE command completed successfully.

I do have dups. Thank you very much. I think we got it now.

Thanks again.....