Results 1 to 7 of 7

Thread: UPDATE column

  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: UPDATE column

    Does anyone know why this update only updates the column specified and makes all other columns null?

    example: I update all US records for sept 11 and they get updated correctly but ALL other records get nulls when they had a previous value????

    Code Below:

    update monthly
    set (lifestage, days_active) = (
    select lifestage, customer_days from lifestage
    where (monthly.accountnumber = lifestage.accountnumber
    and monthly.league = 'US'
    and month = '2011-09-01'))
    GO


    Thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Your SQL is updating every row of the table. You need a predicate on the UPDATE statement.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nadecian View Post
    this update only updates the column specified and makes all other columns null
    Could you demonstrate how you came to that conclusion?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2011
    Posts
    34
    ARWinner - I am using DB2 9.7 (LUW)... i will look into how to predicate on the update statement.

    n_i - I am dumping monthly queries into a table in case I have to go back to the data in a couple months... I updated June data (in July) and all columns had values... I updated July data (in Aug) and every june record was null for the records I updated in Aug.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nadecian View Post
    n_i - I am dumping monthly queries into a table in case I have to go back to the data in a couple months... I updated June data (in July) and all columns had values... I updated July data (in Aug) and every june record was null for the records I updated in Aug.
    I have no idea what it means "to dump a monthly query into a table". From your description all I can say is that one or more of your SQL statements is incorrect.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by nadecian View Post
    ARWinner - I am using DB2 9.7 (LUW)... i will look into how to predicate on the update statement.

    n_i - I am dumping monthly queries into a table in case I have to go back to the data in a couple months... I updated June data (in July) and all columns had values... I updated July data (in Aug) and every june record was null for the records I updated in Aug.
    You definitely need a where clause on the update (yours is on the subquery).

    Andy

  7. #7
    Join Date
    Aug 2011
    Posts
    34
    Thanks ARWinner... worked perfectly.

    n_i > I forgot to specify that I am tracking a small group of customers monthly behavior (qty: 80k) and I am dumping all their transactions into a table incase I am asked to look into other matrix down the road.

    Thanks for the help everyone

Posting Permissions

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