Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have the below DDL and my question is how can I update the MIN() itemlineID in @LineItems with the taxAmt from @Taxinfo without using a CTE?
Declare @LineItems Table
(
ordernumber varchar(100)
,itemlinenumber int
,itemlineID varchar(100)
,tax decimal(16,8)
)
Declare @Taxinfo Table
(
ordernumber varchar(100)
,taxamt decimal(16,8)
)
Insert Into @Taxinfo Values
('123A', '23.88'), ('456B', '2.81')
Insert Into @LineItems Values
('123A', 1, '123A-1'), ('123A', 2, '123A-2'), ('123A', 3, '123A-3'), ('123A', 4, '123A-4')
,('456B', 1, '456B-1'), ('456B', 2, '456B-2')
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Answer accepted by question author
Show the expected results if the next query does not work:
update li1
set tax = taxamt
from @LineItems as li1
inner join @Taxinfo as ti on ti.ordernumber = li1.ordernumber
left join @LineItems as li2 on li1.ordernumber = li2.ordernumber and li1.itemlinenumber > li2.itemlinenumber
where li2.itemlinenumber is null