Update Single row ->latest row

1

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?

2

Please post sample data

3

Hi,

Not sure is this you are looking for. Just an idea of query:

declare @table TABLE(id int,names varchar(50),lastname varchar(50), ukey varchar(50))
insert into @table
select * from (
	select  id=1,names='John',lastname='Smith',ukey='A001' union all
	select  id=2,names='Alex',lastname='Greek',ukey='A002' union all
	select  id=3,names='Peter',lastname='Pan',ukey='A003' union all
	select  id=4,names='Carl',lastname='Jr',ukey='A001' union all
	select  id=5,names='Joe',lastname='Luth',ukey='A005' union all
	select  id=6,names='Sam',lastname='Masterson',ukey='A006' union all
	select  id=7,names='Kim',lastname='Peterson',ukey='A001' union all
	select  id=8,names='John',lastname='Oury',ukey='A005'
)A


update t1
set t1.names=t2.names,
t1.lastname = t2.lastname
from yourtablenamehere t1
join(
	select t.ukey,t.names, t.lastname
	from @table t
	inner join (
		select ukey, max(id) as MaxID
		from @table
		group by ukey
	) tm on t.ukey = tm.ukey and t.id = tm.MaxID
)t2 on t1.ukey=t2.ukey

Thanks.

Regards,
Micheale

4

this is what i need, but oposite , from yourtablenamehere i need to take the latest key.

5

Hi,

I just guessing from your question because a bit confusing on the key explanation part without sample data input and sample data output. Hope above query give you a glance of idea, and you able to write your query to meets your requirements.

If you still have difficulty, it would be glad for you to share a sample input data in tabular format, and output tabular data as expected. Then I can help you to tuning the query exactly what your requirement is.

Happy Coding.

Regards,
Micheale

6

thanks @micnie_2020