Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Unanswered: update difficult

    HI
    I am some difficult on updating my rows on my databases in sql server 2005 , i am searching but i am not yet found .
    if someone know a function or script who help to update : on updating i want to add 1 on each rows
    egs : 1 3 CCh4 after update 11 31 CCh41
    66 99 VBj6 after update 661 991 VBj61
    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please script out your table definition, i.e. column names and datatypes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    4

    there is my table definition

    there is my table definition :
    table name : note(mat(numeric),cod(varchar),ref(varchar))

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE TABLE [note]
       SET mat = mat * 10 + 1
         , cod = cod + '1'
         , ref = ref + '1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2011
    Posts
    4

    update

    no you don't understand me , i want to update on adding to last position
    on field the element 1 and no increasing 1

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    mhmdsw,

    Test-out r937's suggested solution.

    I think you will find that it does exactly what you want.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Dec 2011
    Posts
    4

    update

    just adding 1 for each rows
    egs : if i two rows :
    12 tt1 bby after update 121 tt11 bby1
    6 vv6 nnh after update 61 vv61 nnh1

    just add the element on last position to the field

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mhmdsw View Post
    just add the element on last position to the field
    try my update statement, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2011
    Posts
    9

    try it

    Quote Originally Posted by r937 View Post
    Code:
    UPDATE TABLE [note]
       SET mat = mat * 10 + 1
         , cod = cod + '1'
         , ref = ref + '1'
    IF you means 1 become 11 and asd become asd1 and qwe become qwe1
    the posted solution will solve your problem, first he manipulate the integer number by add place for your new number 1 = 1 * 10 + 1 = 11 right?
    and cod = co + '1' (+) is a concatenation operator so asd = asd + '1' = asd1
    try it.

  10. #10
    Join Date
    Oct 2011
    Posts
    9

    try it

    Quote Originally Posted by r937 View Post
    Code:
    UPDATE TABLE [note]
       SET mat = mat * 10 + 1
         , cod = cod + '1'
         , ref = ref + '1'
    IF you means 1 become 11 and asd become asd1 and qwe become qwe1
    the posted solution will solve your problem, first he manipulate the integer number by add place for your new number 1 = 1 * 10 + 1 = 11 right?
    and cod = co + '1' (+) is a concatenation operator so asd = asd + '1' = asd1
    try it.


    Elmozamil
    Last edited by Pat Phelan; 12-27-11 at 19:12.

  11. #11
    Join Date
    Apr 2007
    Location
    Antwerp
    Posts
    2

    Exclamation

    Try also the SQL2005 documentation :

    1) Learn more about string (char, varchar, ...) concatenation
    + (String Concatenation) (Transact-SQL)
    2) Learn more about arithmetic operator :
    * (Multiply) (Transact-SQL)
    Multiplying by 10 in the decimal world is just as multiply by 2 (decimal) or 10 (binary) in the binary world : just shift all digits 1 position left and put the 0 in the most right open position. After that you add 1 to the numeric value and the 0 in most right position will become a 1.

    Just what u want, or not ?
    if not give us an example that not match !!

Tags for this Thread

Posting Permissions

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