Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Question Unanswered: UPDATE: how much 10+20

    Hi Everybody,
    There are 2 table on MS SQL SERVER 2000:
    t1 (a numeric(9), b numeric(9))
    t2 (a numeric(9), b numeric(9))

    Values:
    t1:
    a=1, b=0
    t2:
    a=1, b=10
    a=1, b=20

    The query:
    UPDATE t1
    SET t1.b=t1.b+t2.b
    FROM
    t1 INNER JOIN t2 ON t1.a=t2.b

    The result:
    t1:
    a=1, b=10

    The MS Access do it correct. (a=1, b=30)

    How can I set the UPDATE to work properly???

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi!

    MS SQL Server does it perfectly correct, because if you try
    Code:
    select t1.b+t2.b
    FROM t1
    INNER JOIN t2 ON t1.a=t2.a
    you get two lines in your result set.

    What you might want to have is something like the following
    Code:
    select t1.b + t2.b
    from t1
    inner join (select a, sum(b) as b from t2 group by a ) t2 on t1.a = t2.a
    Greetings,
    Carsten

  3. #3
    Join Date
    Nov 2003
    Posts
    7

    Re: UPDATE: how much 10+20

    Hi,
    But I have wrote that was a siple example. The original problem is most difficult.
    OK, I try to present it:
    There is a very duplicated table. Many user can add records into it but each record may contain usable information. These data are informations about customers. I can make a primary key and a deduplicated table by personal data (name, birthdate etc). After it I have to join this table with the original one and gather information form it by certain conditions. I always want to compare 2 record and by field decide that I keep the value of the field or change to the other.
    For example:
    There are customers in various state:
    NotStarted,Inprogress,Rejected,Cancelled Or Completed
    Rejected,Cancelled Or Completed are final states. It will not change more.
    I need the last status of each customers. So
    Code:
    Deduplicated customer table:
    t1:
    Name: John Connor
    Birthdate: 03/17/1977
    Status:NotStarted
    ---------------------------
    Name: Sarah Connor
    Birthdate: 08/12/1951
    Status:NotStarted
    
    The original table:
    t2:
    Name: John Connor
    Birthdate: 03/17/1977
    Status:NotStarted
    -------------------------
    Name: John Connor
    Birthdate: 03/17/1977
    Status:InProgress
    ------------------------
    Name: John Connor
    Birthdate: 03/17/1977
    Status:Rejected
    -------------------------
    Name: Sarah Connor
    Birthdate: 08/12/1951
    Status:NotStarted
    -------------------------
    Name: Sarah Connor
    Birthdate: 08/12/1951
    Status:Inprogress
    
    I want in the t1:
    Name: John Connor
    Birthdate: 03/17/1977
    Status:Rejected
    ------------------------
    Name: Sarah Connor
    Birthdate: 08/12/1951
    Status:Inprogress

Posting Permissions

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