Results 1 to 6 of 6

Thread: Update

  1. #1
    Join Date
    Feb 2004
    Location
    Nevada
    Posts
    5

    Unanswered: Update

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Nevada
    Posts
    5

    Update

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

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Update

    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Feb 2004
    Location
    Nevada
    Posts
    5
    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2004
    Location
    Nevada
    Posts
    5

    Talking

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

    Thanks again.....

Posting Permissions

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