CODEWITHSUNDEEP
sqlsql-server
Hussein
Hussein

Reputation: 989

Customized Update parent table data from child table using Case Statement

I have Parent table "Product" contains several products and child table "Activity" which contains the activities applied on the product:

Product Table :

ProductID Status StatusDate
Product#1
Product#2
Product#3
Product#4

Activity Table :

ProductID ActivityName Done Date
Product#1 Installation True 01-01-25
Product#1 Test False
Product#1 Handover False
Product#2 Installation True 05-01-25
Product#2 Test True 15-01-25
Product#2 Handover False
Product#3 Installation True 10-01-25
Product#3 Test True 20-01-25
Product#3 Handover True 30-01-25

I need to update Product Table from Activity Table so the result would be :

ProductNo Status StatusDate
Product#1 Installed 01-01-25
Product#2 Tested 15-01-25
Product#3 HandedOver 30-01-25

So based on last Activity and Activity Date in Child table the Status and StatusDate at Parent table are update

I made several trials without success with the following is closest :

UPDATE Product
SET Status=
        case
            when w.ActivityName='Installation' and w.Done=1 THEN 'Installed'
            when w.ActivityName='Test' and w.Done=1  THEN 'Tested'
            when w.ActivityName='Hand Over' and w.Done=1  THEN 'HandedOver'
        ELSE e.Status
        END,
-------------------------------

StatusDate=
        case
            when w.ActivityName='Installation' and w.Done=1 THEN w.Date
            when w.ActivityName='Test' and w.Done=1  THEN w.Date
            when w.ActivityName='Hand Over' and w.Done=1  THEN w.Date
        ELSE e.Date
        END,

    FROM
        Product e INNER JOIN Activity w
    ON (e.ProductID= w.ProductID)

But the result is some fields are update correctly and others wrong still other fields not updated. What can I try next? I tries this link How to update column coming from TOP 1 of another table with specific criteria and this one Update table with top 1 field from other table but i got same old result and can't customize the status field in Product table as shown in my trial.

Upvotes: -1

Views: 35

Answers (0)

Related Questions