Solved

update fields from one field to another

Posted on 2010-11-30
3
267 Views
Last Modified: 2012-05-10
Hi
I have one table (Items) with the following details
Itemcode, ItemDescription, lastmaintdate

another table Maintenance
MaintReqID, MaintitemCode, MaintDate

I have to for one item multiple maintenance request in table maintenance I need to write a query to update lastmaintdate in table items with the recent date of the maintenance table of the same item.

can you help me with this
0
Comment
Question by:nadermik
3 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34237117
update items
set lastmaintdate = (select max(maintdate) from maintenancem m
where m.maintitemcode=items.itemcode)
0
 
LVL 9

Expert Comment

by:s_chilkury
ID: 34237167
update [items]
set lastmaintdate =
(
      select top 1 maintdate from [maintenance]
      where items.itemcode = maintenance.maintitemcode
      and [whatever clause you need more]
      order by maintdate desc
)
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34237206
update i
set lastmaintdate = (select max(MaintDate) from  Maintenance where MaintitemCode = i.Itemcode)
from items i
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question