Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    47

    Unanswered: how to truncate just a column ?

    Code:
    credit | debit | cash_in_hand
    ------------------------------
    100      100        0
    consider this as my table where i have an requirement
    where i need to truncate just the column cash_in_hand
    but if i do something like this
    Code:
    delete from credit_debit_table where cash_in_hand = '0'
    then it will delete the entire row with credit & debit information
    but i need to delete only cash_in_hand column how to do that ?

    note : i need to delete only data not the structure of cash_in_hand

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    update table set cash_in_hand = NULL ???
    Cheers....

    baburajv

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SQL Server (and all the RDBMSs too) stores data on a row-basis. This means that your requirement does not have any sense. You can put the column data to NULL as already suggested, but this is just a data approach.
    Unless your requirement is about storage space.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by navedjobs View Post
    note : i need to delete only data not the structure of cash_in_hand
    can you explain what you want to happen in terms of the real world as opposed to the database? What you are describing resembles "I want to paint that rain cloud red." which may be possible under some circumstances, but doesn't make much sense and seems like it would require far more effort than it might justify.

    If we could understand what you're trying to accomplish, we can probably offer you a good answer. Based on your description of your problem so far, baburajv has offered the best answer I can imagine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    A variable value like cash in hand ought not to be stored in a table. Create a view to show this as the difference between the credit and debit values, and that way you will always have the current value available.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    weejas, that can be a very expensive query to run time after time. I'm all for dynamically calculating when possible, but performing the same expensive calculations time after time on historical data, always getting the exact same result, begs for storing the data in the row.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    This is true. However, it depends on how many transactions there are and how many times the information is required on a daily basis as to whether the performance hit will be a deal-breaker. For example, I work with two ERPSs, and neither of them store the stock on hand in the inventory.

    Also, whether you're running a query over the whole table to sum transactions for display or running a query over the whole table to sum transactions for storage, you're still running the query over the same table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by weejas View Post
    Also, whether you're running a query over the whole table to sum transactions for display or running a query over the whole table to sum transactions for storage, you're still running the query over the same table.
    Do I need to point out that you run it only once for storage, and it can be performed during off-hours?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    And do I need to point out that if you run such a query overnight, your balances will not reflect any transactions since then? :P

    There are pros and cons to both approaches. I favour calculating such totals as and when they are required, but I accept that it is possible to store them too, and in some cases is preferable.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Apparently I need to point out that it need be run across the table only once, for existing data.
    A trigger or procedure code can keep individual records updated, very efficiently.
    Apparently, that needed to be pointed out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by baburajv View Post
    update table set cash_in_hand = NULL ???
    ding...ding...ding...ding....we have a winner

    Johnny, tell'em what he's won
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dong...dong...dong...dong...dong!
    That answered his question, but did not solve his problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    Dong...dong...dong...dong...dong!
    That answered his question, but did not solve his problem.
    Where did Oppie say that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, read the post history.
    The problem is how best to handle a calculated value such as cash in hand.
    The discussion was whether CashInHand should be stored in the table at all.
    This is clearly pertinent to both the original poster, and others who encounter this thread with similar problems.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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