• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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
0
Bianca
Asked:
Bianca
  • 5
  • 3
1 Solution
 
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
 
BiancaAuthor 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
BiancaAuthor 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
 
BiancaAuthor 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now