Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: Update Count With 1 on First Item. Possible?

    Hello All.

    I need your advise on this .....

    I have a table with Bill_Doc, Bill_Item and Bill_Doc_Count fields. I need to update Bill_Doc_Count with 1 only on the first Bill_Item. Is this possible? I tried min and max but they don't work for me.

    Please help. Thanks a million.

    For example,

    Bill_Doc Bill_Item Bill_Doc_Count
    123 1 1
    123 2 Null
    123 3 Null
    124 1 1
    125 1 1
    125 2 Null

    Best regards

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    couldn't you do this?

    update your_table set bill_doc_count=1 where bill_item=1

    unless bill_item can be any other number... if that's the case, then you can do this:

    update t1
    set bill_doc_count=1
    from your_table t1
    inner join (select bill_doc, bitem=min(bill_item) from your_table group by bill_doc) t2
    on t1.bill_doc=t2.bill_doc and t1.bill_item=t2.bitem

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    How do you determine what is the first bill_item - is it a combination of bill_doc and bill_item ?
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  4. #4
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by ms_sql_dba
    couldn't you do this?

    update your_table set bill_doc_count=1 where bill_item=1

    unless bill_item can be any other number... if that's the case, then you can do this:

    update t1
    set bill_doc_count=1
    from your_table t1
    inner join (select bill_doc, bitem=min(bill_item) from your_table group by bill_doc) t2
    on t1.bill_doc=t2.bill_doc and t1.bill_item=t2.bitem
    Thank you very much. I will give it a try when I step into the office this morning.

  5. #5
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by rnealejr
    How do you determine what is the first bill_item - is it a combination of bill_doc and bill_item ?
    Hi, thank you for replying too.

    To answer to your question, the bill_item is not fixed. Sorry, I should have been more specific. It could be.....

    Bill_Doc Bill_Item
    123 1
    123 2
    123 3
    124 10
    124 20
    125 50
    125 100
    125 150
    126 20
    126 30

    All I need is to pick only 1 Bill_Item from the Bill_Doc. Purpose: to do a Bill_Doc count. Doing a Bill_Item count is easy. Just assign 1 to each record in the table but Bill_Doc is a little tough for me. Looking forward to try out the solution by ms_sql_dba.

    Best regards

  6. #6
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by ms_sql_dba
    couldn't you do this?

    update your_table set bill_doc_count=1 where bill_item=1

    unless bill_item can be any other number... if that's the case, then you can do this:

    update t1
    set bill_doc_count=1
    from your_table t1
    inner join (select bill_doc, bitem=min(bill_item) from your_table group by bill_doc) t2
    on t1.bill_doc=t2.bill_doc and t1.bill_item=t2.bitem
    Hello.

    Thank you very much. Your 2nd solution works PERFECT !!!!!.

Posting Permissions

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