>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?
If you're using SQL Server 2008 or up, I suggest to use your select statement as cte and use MERGE command to do the UPDATE.
If it's not broken, fix it until it is.
My Blog