Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Exclamation Unanswered: Mass update statement

    Is there a syntax error with this statement? It failed!

    update CONTACT CNT set

    CNT.ADDRESS_1_ID =
    (
    select
    CA.CONTACT_ADDRESS_ID
    from
    CONTACT_ADDRESS CA
    where
    CA.CONTACT_ID = CNT.CONTACT_ID
    )
    where
    CNT.ADDRESS_1_ID is null;

    Thanks in advance,

    :?
    Newbie

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Arrow

    Sathyaram,

    Thanks as usual for the prompt reply. I did alter my statement to

    Code:
    update CONTACT CNT set ADDRESS_1_ID = (select CA.CONTACT_ADDRESS_ID from CONTACT_ADDRESS CA, CONTACT CNT where CA.CONTACT_ID = CNT.CONTACT_ID);

    It gave me an

    Error:
    SQL0104N An unexpected token "set" was found following "<identifier>".
    Expected tokens may include: "USING". SQLSTATE=42601

    Any pointers? TIA.

    ~ Newbie

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I think that is bit misleading ...

    Try this:

    update CONTACT CNT set ADDRESS_1_ID = (select CA.CONTACT_ADDRESS_ID from CONTACT_ADDRESS CA where CA.CONTACT_ID = CNT.CONTACT_ID);
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Arrow Sathyaram, Jim

    Code:
    update CONTACT CNT set ADDRESS_1_ID = (select CA.CONTACT_ADDRESS_ID from CONTACT_ADDRESS CA where CA.CONTACT_ID = CNT.CONTACT_ID);

    did not work.

    But
    Code:
    update CONTACT CNT set ADDRESS_1_ID = (select max(CA.CONTACT_ADDRESS_ID) from CONTACT_ADDRESS CA where CA.CONTACT_ID = CNT.CONTACT_ID);

    worked, populating all the ADDRESS_1_ID column with same data (maximum of CONTACT_ADDRESS_ID ?! )

    I know its weird, i am still curious if there was a way to dynamically update multiple records.

    Thanks for all your help,

    Newbie

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Maybe I am wrong, but I thought there were some update enhancements in recent fixpaks. What release and fixpak are you on?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Post

    DB2 8.1.5 (fixpack 5) running on Windows 2000.

    Is it possible? Any pointers in this regards would be really helpful.

    Thanks,
    Newbie

Posting Permissions

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