I know that typically you would have an autonumber field – but in this instance there is not, and it honestly does not matter which row get's updated as long as it is only 1.
I have sample DDL like such one table that contains requested qty, and one table that contains avaliable qty. I want to update @Test1.amtonhand
with the value from @Test2.amtonhand
and join on @Test1.nrs=@Test2.nrs
but only update the 1 record in @Test1
How is this achieved in SQL Server 2008
Declare @Test1 Table
(
nrs varchar(100)
,amtrqst varchar(100)
,amtonhand varchar(100)
)
Declare @Test2 Table
(
nrs varchar(100)
,amtonhand varchar(100)
)
Insert Into @Test1 (nrs, amtrqst) Values
('abc', '10'), ('abc', '12'), ('abc', '13'), ('def', '5'), ('def', '12')
Insert Into @Test2 (nrs, amtonhand) Values
('abc', '10'), ('def', '5')
EDIT
My desired results from the update would be
@Test1 --
abc, 10, 10
abc, 12
abc, 13
def, 5, 5
def, 12
Best Answer
If you don't care which row gets updated, you can use
ROW_NUMBER
with an arbitrary order:The results are: