UPDATE from a DERIVED QUERY - SQL

Question

I have an update query to run where the final scalar value is based on a derived query. I need to make sure the correct row is updated in the outermost query. How can I join from the DERIVED2 query on RxTransactionID to the outermost query in this example?

NOTE: The 'A.RxTransactionID' in this WHERE clause is not allowed. WHERE Fact.Adherence.RxTransactionId = A.RxTransactionID

UPDATE Fact.Adherence SET Fact.Adherence.LAST_MPR = (

SELECT LAST_MPR FROM
(
SELECT
DERIVED1.IRXLocationId, DERIVED1.RxNumber, DERIVED1.RefillNumber, DERIVED1.RxTransactionID, DERIVED1.ClientId, DERIVED1.CompletedDate, DERIVED1.NextCompletedDate,
LAST_MPR = NULLIF(CAST(CAST(COALESCE(DaysSupply,    0) as decimal)  / NULLIF(CAST(COALESCE(DATEDIFF(DAY, DERIVED1.CompletedDate, DERIVED1.NextCompletedDate), 0) as decimal), 0)as decimal(12,4)), 0)
FROM
    (SELECT
    [CompletedDate] As [CompletedDate],
    NextCompletedDate = (SELECT  -- Will be the second most recent complete date.
    TOP 1 AA.[CompletedDate] FROM Fact.RxTransaction AA WHERE AA.RxId = C.RxId AND COALESCE(A.RefillNumber + 1,0) = AA.RefillNumber AND AA.RxID = C.RxID),
    A.RxNumber, [DaysSupply], [RefillNumber], C.[RxID], A.RxTransactionID, A.ClientId, A.IRXLocationId

    FROM [Fact].[Adherence] A
    INNER JOIN Dimension.RX C on A.RxId = C.RxId
    WHERE A.CompletedDate > DATEADD(MONTH, -6, GETDATE())
    ) DERIVED1
ORDER BY IRXLocationId, RxNumber, RefillNumber desc) DERIVEDA
)

WHERE Fact.Adherence.RxTransactionId = A.RxTransactionID

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

If you put the derived values and a lookup key to Fact.Adherence into a temp table you can do an update statement on a join of those two tables.

There's a good example on how to do update joins here: Update Join Example

edited May 23 '17 at 11:51

Community

1
1

answered Jun 02 '15 at 14:58

MartianCodeHound

355
1
8

Slight modifications to the original query to remove the outer update query... and added your suggestion... running now. Should work. UPDATE AD SET AD.LAST_MPR = T.LAST_MPR FROM Fact.Adherence AD INNER JOIN #TEMP T ON AD.RxTransactionId = T.RxTransactionId – Rob Gaudet Jun 02 '15 at 17:18

UPDATE from a DERIVED QUERY - SQL

1 Answers1