Using Update between two tables

• cvrupesh

  SSC Rookie

  Points: 46

  More actions

  May 13, 2009 at 10:28 pm

  #200537

  Hi All,

  I am having a table named item_data where in item and active_date are two columns.

  Another table is Error_log where the columns are item,store,tran_date.

  I want to update active_date for only 5 items in item_data table with minimum value of tran_date for that particular item in Error_log table.

  One solution i had is get item,min(tran_date) from Error_log table and then manually update the active_date in item_date for each and every item.

  I am looking for a query which will do this process in a single shot by looking into the two tables provided the only 5 items.

  Can anyone help me in this?

  Thanks,

  Rupesh.

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  May 15, 2009 at 12:50 pm

  #995589

  cvrupesh (5/13/2009)

  ---

  Hi All,

  I am having a table named item_data where in item and active_date are two columns.

  Another table is Error_log where the columns are item,store,tran_date.

  I want to update active_date for only 5 items in item_data table with minimum value of tran_date for that particular item in Error_log table.

  One solution i had is get item,min(tran_date) from Error_log table and then manually update the active_date in item_date for each and every item.

  I am looking for a query which will do this process in a single shot by looking into the two tables provided the only 5 items.

  Can anyone help me in this?

  Thanks,

  Rupesh.

  I would recommend you ask your question here. It is not quite related to SSIS.

  ---
  SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  

• FelixG

  SSCrazy

  Points: 2550

  More actions

  May 15, 2009 at 1:59 pm

  #995657

  cvrupesh (5/13/2009)

  ---

  Hi All,

  I am having a table named item_data where in item and active_date are two columns.

  Another table is Error_log where the columns are item,store,tran_date.

  I want to update active_date for only 5 items in item_data table with minimum value of tran_date for that particular item in Error_log table.

  One solution i had is get item,min(tran_date) from Error_log table and then manually update the active_date in item_date for each and every item.

  I am looking for a query which will do this process in a single shot by looking into the two tables provided the only 5 items.

  Can anyone help me in this?

  Thanks,

  Rupesh.

  If I understand you, this will accomplish your job....

  Update A set Active_date= Min_Date

  from Item_Data A

  inner join

  (select top 5 item, min(tran_date) as Min_date from error_log group by item order by min(tran_date)) B

  on a.item=b.item