1

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
  • You should add in desired results. – Erik Darling Nov 13 '17 at 15:14
  • @sp_BlitzErik - see my edit, does that help? – BellHopByDayAmetuerCoderByNigh Nov 13 '17 at 15:17
  • But, abc, 10, NULL and abc, 13, 10 would be equally valid? (That is to say, it doesn't matter whether or not the first row you happened to list is the row that gets updated?) – RDFozz Nov 13 '17 at 23:03
4

If you don't care which row gets updated, you can use ROW_NUMBER with an arbitrary order:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY nrs ORDER BY (SELECT NULL))
    FROM @Test1
)
UPDATE a
SET a.amtonhand = b.amtonhand 
FROM CTE a
INNER JOIN @Test2 b
    ON a.nrs = b.nrs
WHERE a.RN = 1;

The results are:

╔═════╦═════════╦═══════════╗
║ nrs ║ amtrqst ║ amtonhand ║
╠═════╬═════════╬═══════════╣
║ abc ║      10 ║ 10        ║
║ abc ║      12 ║ NULL      ║
║ abc ║      13 ║ NULL      ║
║ def ║       5 ║ 5         ║
║ def ║      12 ║ NULL      ║
╚═════╩═════════╩═══════════╝

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.