2

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 @[email protected] 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
3
  • You should add in desired results. Nov 13, 2017 at 15:14
  • @sp_BlitzErik - see my edit, does that help? Nov 13, 2017 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, 2017 at 23:03

1 Answer 1

5

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 and acknowledge you have read our privacy policy.

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