Simple T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Simple T-SQL

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2011-04-07 : 16:58:33

Okay, its probably easy for some gurus like you.

I have an item table. On this item table I have a field that I want to regularly update with T-SQL from querying underlying data. The underlying data is a sales ledger that tracks all of the sales of any given item. A field on the sales ledger is the margin of the sale for each item. Any given item can be listed multiple times on the transaction ledger. I want the field on the item table to be updated with the most recent sale of a given item. So, it item A has sold 4 times, I want the margin from the most recent transaction to update back to the desired field on the item table.

Here is what I have so far:

UPDATE dbo.ITEMTABLE Set MARGIN=X.Margin
FROM
(
SELECT Item, Margin, Date
FROM ORDERSDB.dbo.FinancialReport
WHERE Customer = 'OS'
ORDER BY Date DESC
)X
WHERE
dbo.ITEMTABLE.ITEMNUMBER=X.Item

Any help somebody can offer out there?

Thanks a million in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 20:14:31

Here is a way of doing it. The query below parses, but I did not test it, so please, please, if you are going to try it, try it in a test environment.

with CTE as
(
   select 
      Item,
      Margin, 
      row_number() over (partition by Item order by Date desc) as rowId
   from 
      ORDERSDB.dbo.FinancialReport
   -- where  -- if you want to do this only for customer "OS", uncomment these two lines.
   --   customer = 'OS'
)
update i set 
   Margin = c.Margin
from
   CTE c
   inner join dbo.ITEMTABLE i 
      on i.Item = c.Item and c.rowId = 1;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 20:21:06

Seemed like malpractice to post code without testing, so I created some test data and tested it. The only change from your schema is that I created the FinancialReport table in the same database instead of the ORDERSDB

create table dbo.ITEMTABLE( item int, margin float);
create table dbo.FinancialReport(Item int, margin float, date datetime);

insert into dbo.ITEMTABLE values (1, null);
insert into dbo.ITEMTABLE values (2, null);
insert into dbo.ITEMTABLE values (3, null);

insert into dbo.FinancialReport values (1, 0.7, '20110101');
insert into dbo.FinancialReport values (1, 0.3, '20110107');
insert into dbo.FinancialReport values (1, 0.1, '20110103');

insert into dbo.FinancialReport values (2, 0.8, '20110203');
insert into dbo.FinancialReport values (2, 0.1, '20110403');

And it works, until you tell me otherwise

benking9987
Posting Yak Master

124 Posts

Posted - 2011-04-11 : 14:48:59

YES YES YES. You did it. Thank you. Of course I had to modify some of the sytax to match my schema exactly, but this was definitely the strategy that worked.

Thank you thank you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 15:16:07

You are very welcome - I am very glad the strategy worked, and more importantly, that I didn't commit malpractice.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-11 : 15:22:39

I'm suing.

Corey