>I have a SQL like this:
>
>

>SELECT InvoiceDetail.AddDate,* FROM Invoice
>CROSS APPLY (SELECT TOP 1 InvoiceDetail.Numero,InvoiceDetail.AddDate
>FROM InvoiceDetail
>WHERE InvoiceDetail.NoInvoice=Invoice.Numero
>ORDER BY InvoiceDetail.Numero DESC) InvoiceDetail
>WHERE Invoice.Status='1899-12-30 00:00:00.000'
>

>
>This serves for identifying the related Invoice records having an empty value in the Status field. Because of a place in the application where the code was not correct, some records gets added without a value in that field. However, we have the value in the InvoiceDetail transaction table, which contains several detail records to that invoice, and the last one contains the timestamp I need to plug in my master table.
>
>So, I need to create an UPDATE INNER JOIN related SQL Command to do that based on that SQL. The closes I have found so far is something like this but this is not complete:
>
>

>UPDATE Invoice SET Status=DispatchDetail.Status
> INNER JOIN ... on ....NoInvoice=Invoice.Numero
>SELECT InvoiceDetail.AddDate,* FROM Invoice
>CROSS APPLY (SELECT TOP 1 InvoiceDetail.Numero,InvoiceDetail.AddDate
>FROM InvoiceDetail
>WHERE InvoiceDetail.NoInvoice=Invoice.Numero
>ORDER BY InvoiceDetail.Numero DESC) InvoiceDetail
>WHERE Invoice.Status='1899-12-30 00:00:00.000'
>

>
>Can anyone complete this SQL to give me an example on such UPDATE with an INNER JOIN including a CROSS APPLY?

UPDATE Invoice SET Status=DispatchDetail.Status
FROM Invoice
 INNER JOIN (SELECT InvoiceDetail.AddDate,*
                    FROM Invoice
             CROSS APPLY (SELECT TOP 1 InvoiceDetail.Numero,InvoiceDetail.AddDate
                                 FROM InvoiceDetail
                          WHERE InvoiceDetail.NoInvoice=Invoice.Numero
                          ORDER BY InvoiceDetail.Numero DESC) InvoiceDetail
             WHERE Invoice.Status='1899-12-30 00:00:00.000') InvDet
      on InvDet.NoInvoice=Invoice.Numero

?