> Without thinking about it too hard (which means this may or may not be
> the real issue) it looks like you're missing a couple of 'as' clauses:
>
> update account as a ... from history as h ...
>
Unfortunately, AS is a voluntary addition to SQL statements. I would
love to see them made compulsory... ;)
Jeff,
>
>
> > I guess I am having an off day with SQL. I think technically this should
> > work but I get a syntax error
> >
> >
> > UPDATE Account a
> > SET a.last_update = (SELECT TOP 1 h.tranDate
> > FROM History h
> > WHERE a.Account_Number =
> h.Account_Number
> > ORDER BY h.tranDate DESC)
> >
Umm... Isn't this going to update last_update on every record in Account
to be the one record from History that you get?
You're probably looking for something more like :
UPDATE Account AS a
LEFT JOIN History AS h
ON a.Account_Number = h.Account_Number
SET a.last_update = h.tranDate
But even thats not quite right....
Just a thought..... The transaction history table isn't going any place
is it? If its sticking around, then why do you need to duplicate the
data into the account table? You can just SELECT it out at the
appropriate time.
SELECT Account.*, MAX(History.tranDate) AS Last_Update
FROM Account
LEFT JOIN History ON Account.Account_Number = History.Account_Number
WHERE Account.Account_Number = 123
Or if you really do need to duplicate the data into the account table,
why not do at the time of writing the record to the history table?
Hope that helps
Stephen
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
