Update query question 1

[Sylvor]

Hi!

I am trying to update one field from another, the trouble is the field I want to copy from has multiple values.

I want to update the "date last invoiced" field in my customer table from the "invoice date" field in the invoices table. The customer table has one record for each customer but the invoices table could have multiple records for each customer (because it holds invoices!)

Data from Customer table
Customer Number
Date last invoiced

Data from Invoices table
Customer Number
Invoice Date

The query I am using is below, which works to a degree... unfortunately the "date last invoiced" data is updated with the "invoice date" in no particular order... what I want is the MAXIMUM date, or the latest date.

I cannot use max(... on an update routine, does anyone have any ideas?


update dbo.SL_ACCOUNTS set CU_DATE_INV = SL_TRANSACTIONS.ST_DATE
FROM HigherNature.dbo.SL_ACCOUNTS SL_ACCOUNTS INNER JOIN HigherNature.dbo.SL_TRANSACTIONS SL_TRANSACTIONS ON SL_ACCOUNTS.CUCODE=SL_TRANSACTIONS.ST_COPYCUST


Thanks,
Dave

 

[JamesLean]

Try this:

UPDATE c
SET lastinvoice = i.maxdate
FROM customers c JOIN (
  SELECT custnum, MAX(invoicedate) AS maxdate
  FROM invoices
  GROUP BY custnum
) i ON c.custnum = i.custnum

--James

 

[maswien]

try:

update dbo.SL_ACCOUNTS set CU_DATE_INV = SL_TRANSACTIONS.ST_DATE
FROM HigherNature.dbo.SL_ACCOUNTS SL_ACCOUNTS INNER JOIN
( select ST_COPYCUST, max(SL_TRANSACTIONS.ST_DATE) as
ST_DATE from HigherNature.dbo.SL_TRANSACTIONS
) as SL_TRANSACTIONS
ON SL_ACCOUNTS.CUCODE=SL_TRANSACTIONS.ST_COPYCUST

 

[Sylvor]

Perfect - had to add a group by option to the select statement, but it worked fine!

Thanks loads!

Dave Bennett