Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Column update from multiple tables

    I am having a hard time updating this column. im not sure why its not working??? its running for a long time and it shouldn't. (fyi the accountnumber on dailyorders is a varchar and an int on lifestage)

    I am basically added a column to a table to track how many days a customer is being active.


    UPDATE lifestage
    SET lifestage.days =
    ( select count(distinct(d.datesettled))
    from dailysorders d
    where lifestage.accountnumber = d.accountnumber
    and d.eventypecode IN ('VIP', 'ELITE')
    and d.datesettled BETWEEN '2012-07-01' AND '2011-07-31'
    group by accountnumber)
    GO

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is this DB2 or SQL Server?

    1) you are updating every row int the table in one statement. This can take a while depending on the number of rows.
    2) you do not need the group by clause
    3) lifestage.days will always be 0 after this statement (check your date range).

    Andy

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by nadecian View Post
    I am having a hard time updating this column. im not sure why its not working??? its running for a long time and it shouldn't. (fyi the accountnumber on dailyorders is a varchar and an int on lifestage)

    I am basically added a column to a table to track how many days a customer is being active.


    UPDATE lifestage
    SET lifestage.days =
    ( select count(distinct(d.datesettled))
    from dailysorders d
    where lifestage.accountnumber = d.accountnumber
    and d.eventypecode IN ('VIP', 'ELITE')
    and d.datesettled BETWEEN '2012-07-01' AND '2011-07-31'
    group by accountnumber)
    GO
    I don't know the answer to your question, but it is usually a bad sign when there is a where predicate in the set clause that is missing in the where clause of the update. You will probably end up with a number of nulls in lifestage, perhaps deliberate, perhaps not?
    --
    Lennart

Posting Permissions

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