Need to update a field in table with data from other table, only the most current date

I have to update the Last_Assigned_SalesRep_DT field from tblContractor which needs the Last_Edit_Date (most recent date) from tblContractorSalesRepHistory. There are multiple Last_Edit_Date entries and I only need the most current one. Here is what I have tried but am getting an error:

UPDATE contractor SET contractor.Last_Assigned_SalesRep_DT
FROM (SELECT Last_Edit_Date FROM ContractorSalesRepHistory
               WHERE Contractor.id = ContractorSalesRepHistory.ExternalID
               ORDER BY Last_Edit_Date DESC)
WHERE Contractor.id = ContractorSalesRepHistory.Externalid
Norm-alNetwork EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
This should do:

update c
set last_assigned_salesrep_dt = m_lde
from contractor c
join (select externalid as id,max(last_edit_date) m_lde from contractorsalesrephistory group by externalid) e
on c.id=e.id
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Norm-alNetwork EngineerAuthor Commented:
i get this error...

Column or expression 'Last_Assigned_SalesRep_DT' cannot be updated.
0
BrandonGalderisiCommented:
Run this:

exec sp_help contractor


is last_assigned_salesrep_dt a computed column?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Norm-alNetwork EngineerAuthor Commented:
it's not a computed column. running that exec gave me:
Name - Contractor
Owner - dbo
Type - user table
CreatedDateTime - 5/20/08 10:00am
0
BrandonGalderisiCommented:
That's not ALL the output it gives you.  That's just the first part.  The next section has the columns listed.  The second recordset will give you column_name, type, "COMPUTED" and more columns.  Look at the last_assigned_salesrep_dt ROW and look at the COMPUTED column.


"Column or expression 'Last_Assigned_SalesRep_DT' cannot be updated." makes me think it's a computed column.
0
BrandonGalderisiCommented:
OR...  you have NULL values in your derived query and your Contractor table doesn't allow NULL.

select * from (select externalid as id,max(last_edit_date) m_lde from contractorsalesrephistory group by externalid) e
where m_lde is null
0
Norm-alNetwork EngineerAuthor Commented:
when i ran exec sp_help contractor

i got this: The EXEC SQL construct or statement is not supported.

and the only data that i saw is what i stated above.

I ran that last select statement and got just the column names "id" and "m_lde" but nothing else.

thanks for your help...
0
BrandonGalderisiCommented:
Are you running this in Enterprise manager's query builder or in SQL Query Analyzer?  You have to run it in SQL Query Analyzer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.