Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2005
    Posts
    36

    Unanswered: update from selected row

    Hi,

    I am having a problem updating one field in a table the update should be the product of two other fields from the same row.
    There are atleast 3000+ records need to be updated here.


    e.g.

    update A
    set A.b = A.c * A.D

    here b c and d are from same row .. I was wondering if someone knows how to solve this problem.

    Thanks in advance.
    Last edited by azshah; 04-30-07 at 15:53.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    And what is the problem?

    There's nothing that implicitly stops you from doing this:

    Code:
    CREATE TABLE #ProductTest (col1 int, col2 int, col3 int)
    
    INSERT INTO #ProductTest VALUES (2, 3, 0)
    
    UPDATE #ProductTest
    SET col3 = col1 * col2
    
    SELECT * FROM #ProductTest
    
    DROP TABLE #ProductTest
    ----------------------------
    (1 row(s) affected)
    
    col1        col2        col3        
    ----------- ----------- ----------- 
    2           3           6
    
    (1 row(s) affected)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2005
    Posts
    36
    Sorry I was not very clear before , Thanks for the reply the problem here is that there are atleast 3000 + rows that need to be updated at once and the valuse for column c and d are different for each row since a = b * c for only that particular row.
    Last edited by azshah; 04-30-07 at 15:52.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok....

    Code:
    CREATE TABLE #ProductTest (col1 int, col2 int, col3 int)
    
    INSERT INTO #ProductTest VALUES (2, 3, 0)
    INSERT INTO #ProductTest VALUES (3, 4, 0)
    INSERT INTO #ProductTest VALUES (4, 5, 0)
    
    UPDATE #ProductTest
    SET col3 = col1 * col2
    
    SELECT * FROM #ProductTest
    
    DROP TABLE #ProductTest
    -------------------------
    (3 row(s) affected)
    
    col1        col2        col3        
    ----------- ----------- ----------- 
    2           3           6
    3           4           12
    4           5           20
    
    (3 row(s) affected)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2005
    Posts
    36
    Thanks again ....it does work fine but when I do a calculation for 66/100 it gives me the result 0 instead of 0.66 any idea why

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    what datatype is your destination column. Integers don't have decimal points...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2005
    Posts
    36
    the value 66 is comming from int data type but 66/100 will go into money datatype... I guess I would need to somehow convert 66 to double ...the destination colmn is money
    (66.00/100) will give me 0.66
    I believe if I can find out how to cast int to double it might solve my problem here
    Last edited by azshah; 04-30-07 at 16:19.

  8. #8
    Join Date
    Apr 2005
    Posts
    36
    Yup casting has solved my problem ....
    cast (66 as float) /100

    Thank you very much Teddy really appreciated your help here.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Anytime. Implicit datatype conversion bugs are a PITA.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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