SQL Server Performance

Update 2 columns

Discussion in 'T-SQL Performance Tuning for Developers' started by luma, Aug 19, 2005.

  1. luma New Member

    Hallo , How can I update values in two columns in the same row ? Is it possible ?
    For example :
    .
    .

    set @ident =@Id
    .
    .
    .

    UPDATE links SET dowNum=@newStart , uppNum=@newEnd WHERE @Id =@ident


    dowNum , uppNum ....are names of columns
    @newStart , @newEnd .... are values what i want to update in this columns


    Thanks, Lubo
  2. Adriaan New Member

    The UPDATE part is correct, but your WHERE clause doesn't refer to a column. This means that the update affects all records if @Id and @Ident are identical, and else no records are updated.
  3. luma New Member

    BUt I want to update values during run in Cursor - Fetch , row by row.
  4. Madhivanan Moderator

    Post the table structure and explain what you are trying to update with sample data



    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    quote:Originally posted by luma

    BUt I want to update values during run in Cursor - Fetch , row by row.
    Any reason why you must use a cursor?

    Look at BOL > Transact SQL Reference > DECLARE CURSOR for the full syntax.
  6. luma New Member

    I have table linky with columns : id, linky , dolneCislo, horneCislo.

    Structure :
    id ... int ..identity
    linky....varchar (255)
    dolneCislo .....decimal(15)
    horneCislo.....decimal(15)


    I read value from column linky row by row in the cursor.
    Then i need update columns dolneCislo and horneCislo with some value (changed value from column linky) in actual row (the same row )


    Conversion values is OK !!!!!!
    All rows are updated with values of last row in the table.

    When i change command Insert (instead Of Update) - insert to other table , written value in other table are correct
    Apologize my english , Lubo
  7. Madhivanan Moderator

    Can you post some sample data and the result you want?


    Madhivanan

    Failing to plan is Planning to fail
  8. mmarovic Active Member

    Maybe you want this:

    declare @start int
    declare @end int
    --
    select @start = dolneCislo, @end = horneCislo
    from linky
    order by id
    --
    update linky
    set dolneCislo = @start, horneCislo = @End
  9. luma New Member


    Values in Linky - there are different lenght . Data in row of table

    row 1....12345
    row 1....456
    row 1....789
    row 1....3

    then i need convert and write to columns dolneCislo, horneCislo min values and max to lenght 5 digit and missing digit set as 0 or 9.

    Result
    Id linky dolneCislo horneCislo
    1 12345 12345 12345
    2 456 45600 45699
    3 789 78900 78999
    4 6 60000 69999

    Conversion is correct
  10. mmarovic Active Member

    I hope i got it right this time:

    update linky
    set dolneCislo = substring(cast(dolneCislo as varchar(5)) + '00000', 1, 5))
    horneCislo = substring(cast(horneCislo as varchar(5)) + '99999', 1, 5))
  11. Madhivanan Moderator

    quote:Originally posted by mmarovic

    I hope i got it right this time:

    update linky
    set dolneCislo = substring(cast(dolneCislo as varchar(5)) + '00000', 1, 5))
    horneCislo = substring(cast(horneCislo as varchar(5)) + '99999', 1, 5))
    Small Correction


    update yourTable set dolneCislo = substring(cast(linky as varchar(5)) + '00000', 1, 5),
    horneCislo = substring(cast(linky as varchar(5)) + '99999', 1, 5)


    Madhivanan

    Failing to plan is Planning to fail
  12. Madhivanan Moderator

    Luma, Did you get solution?


    Madhivanan

    Failing to plan is Planning to fail
  13. mmarovic Active Member

    Thanks, Madhivanan.
  14. luma New Member

    Yes , thanks , Lubo

Share This Page