asked on

update table from another table join

Hi experts
i have 2 tables i need to update the first table depend on the last record from the second table

Table 1
Reference Date Complete Date
---------------------------------------------------------------------------
42275        24-Jan-2011 09:33 AM        NULL
338402        27-Mar-2010 08:03 AM        NULL

Table 2
Id Reference Status Date Complete Date
-----------------------------------------------------------------------------------------------------------------------------
26710      42275        Task Assignment      24-Jan-2011 09:53 AM        03-Feb-2011 08:00 AM
25365      42275        Task Approval            24-Jan-2011 09:33 AM        24-Jan-2011 09:53 AM
25361      42275        Use this form      24-Jan-2011 09:19 AM        24-Jan-2011 09:33 AM
2831      338402        Task Approval        27-Mar-2010 08:03 AM 28-Mar-2010 08:03 AM
2768      338402        Use this form            27-Mar-2010 07:33 AM 27-Mar-2010 07:55 AM

i need to update [Table 1].[Complete Date] with last record from [Table 2].[Complete Date] depend on Maximum [Table 1].[Id] and [Table 1].[Reference] = [Table 2].[Reference]

can you please help me in writing the Query

Guy Hengel [angelIII / a3]

this article should help:
https://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

in short:

update t1
   set t1.[Complete Date] = ( select t2.[Complete Date]  from [table 2] t2
                                   where t2.id = ( select max(x.id) from [table 2] x where x.[Reference] = t1.[Reference] )
                                         and t2.Reference = t1.Reference
                      )
  from [table 1] t1

Open in new window

AZZA-KHAMEES

ASKER

thank you for the reply, but i am getting the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial