CODEWITHSUNDEEP
sqlsql-servert-sql
BravaDejana
BravaDejana

Reputation: 48

Update Column in table using Select, Inner Join and Case When

I have a table called: Claim, where I've already moved data from another table called Damage. However, today I needed to add a column in Claim called ClaimStatusID. And to give values to ClaimStatusID I need to to it based on another column called DamageApprovedStatusID, which exists in yet another table called DamageErrand. Damage and DamageErrand has relation through a crosstable called DamageErrandCrossDamage. In that crosstable DamageErrandID and DamageID exists. Here, I've commented more information:

-- Claim has column ID which has the same ID as the table Damage
-- DamageErrand has DamageApprovedStatus ID which need to be inserted
-- to ClaimStatusID, where the ID's need to be correct.
-- Table Damage and Table DamageErrand has a cross table:
-- DamageErrandCrossDamage, where both of their ID's are stored
-- Claim.ID should therefore be the same as DamageErrandCrossDamage.DamageID
-- Since Claim.ID has the same ID as Damage.ID
-- IF DamageApprovedStatus = -1, SET ClaimStatusID = 0
-- IF DamageApprovedStatus = 0, SET ClaimStatusID = 4
-- IF DamageApprovedStatus = 1, SET ClaimStatusID = 2

And here's a SQL query I created for selecting and using Case When to give the correct statuses:

SELECT Claim.ID as claimid, DamageErrandID as damageerrandeid,
CASE --
WHEN DamageErrand.DamageApprovedStatusID = -1 THEN 0
WHEN DamageErrand.DamageApprovedStatusID = 0 THEN 4
WHEN DamageErrand.DamageApprovedStatusID = 1 THEN 2
ELSE '-'
END AS DamageApprovedStatusID,
DamageApprovedStatusID
FROM
DamageErrand
INNER JOIN DamageErrandCrossDamage ON DamageErrand.ID = 
DamageErrandCrossDamage.DamageErrandID
INNER JOIN Claim ON DamageErrandCrossDamage.DamageID = Claim.ID
WHERE Claim.ID = DamageErrandCrossDamage.DamageID

I do believe this is correct, with the ID's matching and everything. But how can I actually update the table Claim with this? I know insert won't work since I have columns that does not allow null in Claim table. I tried doing something similar to:

--UPDATE Claim
--SET ClaimStatusID = 
--(SELECT DamageApprovedStatusID FROM
--DamageErrand 
--INNER JOIN DamageErrandCrossDamage ON DamageErrand.ID = 
-- DamageErrandCrossDamage.DamageErrandID
--INNER JOIN Claim ON DamageErrandCrossDamage.DamageID = Claim.ID
--WHERE Claim.ID = DamageErrandCrossDamage.DamageID)

But obviously this won't work either. Really thankful for any help! Btw: I'm using SQL-server for this.

Upvotes: 0

Views: 345

Answers (1)

user8103263
user8103263

Reputation:

I believe something like this should work:

UPDATE Claim
    SET ClaimStatusID = alias.DamageApprovedStatusID
FROM (
    SELECT DamageErrandCrossDamage.DamageID, 
        CASE 
            WHEN DamageErrand.DamageApprovedStatusID = -1 THEN 0
            WHEN DamageErrand.DamageApprovedStatusID = 0 THEN 4
            WHEN DamageErrand.DamageApprovedStatusID = 1 THEN 2
        ELSE '-'
        END AS DamageApprovedStatusID
    FROM DamageErrand 
        INNER JOIN DamageErrandCrossDamage ON DamageErrand.ID = DamageErrandCrossDamage.DamageErrandID
) alias
WHERE Claim.ID = alias.DamageID

I guess it could be rewritten like this too:

UPDATE Claim
    SET ClaimStatusID = CASE 
                            WHEN DamageErrand.DamageApprovedStatusID = -1 THEN 0
                            WHEN DamageErrand.DamageApprovedStatusID = 0 THEN 4
                            WHEN DamageErrand.DamageApprovedStatusID = 1 THEN 2
                            ELSE '-' 
                        END
FROM DamageErrand
    INNER JOIN DamageErrandCrossDamage ON DamageErrand.ID = DamageErrandCrossDamage.DamageErrandID
    INNER JOIN Claim ON DamageErrandCrossDamage.DamageID = Claim.ID

Upvotes: 1

Related Questions