none
Update Single row which is latest row by ID RRS feed

  • Question

  • i have Stored Procedure which get table type,
    name varchar(50)
    last name (varchar(50)
    key varchar(50)
    and Sql Table with the same fields.
    id int identity
    name varchar(50)
    last name (varchar(50)
    key varchar(50)
    i want to update the first@last name based on the key.
    the key in the tablle can return on itself, but i want to take the row with the latest key
    where the ID is the ighest (means find the last id with this key)
    and update the first & last name.
    how to do this?
    Wednesday, July 24, 2019 6:29 AM

All replies

  • DECLARE @id INT;
    
    SELECT @id = TOP(1) id
    FROM sql_table
    WHERE key = @key
    ORDER BY id DESC;
    
    UPDATE sql_table
    SET name = @name, lastname = @lastname
    WHERE id = @id;


    Martin Cairney SQL Server MVP

    Wednesday, July 24, 2019 6:42 AM
    Answerer
  • i have Stored Procedure which get table type,  (...)
    and Sql Table with the same fields. (...)
    i want to update the first@last name based on the key.   (...)
    where the ID is the highest (means find the last id with this key)

    Considering that @TVP is the name of the parameter of type table-valued parameter and tbSqltable is the name of the table, here is a suggestion:

    -- code #1
    ;with Leitura as (
    SELECT S.id, P.name, P.[last name]
      from @TVP as P
           outer apply (SELECT max (T.id) as id
                          from tbSqltable as T
                          where T.key = P.key) as S
    )
    UPDATE S
      set name= L.name, [last name]= L.[last name]
      from tbSqltable as S
           inner join Leitura as L on L.id = S.id;
     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Saturday, August 17, 2019 10:30 PM
    Answerer