CODEWITHSUNDEEP
sqlsql-server
KDorn
KDorn

Reputation: 1

is there a way to update a column using multiple tables

I'm trying to update a column called Specialty in a table called #LIST. I need to pull information from 3 different tables incase the specialty in 1 table is NULL. I prefer to use the specialty from the #Segment table, if that's NULL then I want to use the specialty from the #SUBMARKET table, and if that's NULL I want to use the specialty from the #MARKET table.

When I run the below query, it doesn't update any rows. I don't want to use more than 1 Update statement. What can I do?

UPDATE  #LIST   
SET Specialty = CASE WHEN   (l.Segment__c is not NULL)  THEN    s.Specialty
                    WHEN    (l.Segment__c is NULL)      THEN    b.Specialty
                    WHEN    (l.Submarket__c is NULL)    THEN    m.Specialty 
                    ELSE s.Specialty 
                END 
FROM #LIST l 
    join #MARKET m on m.Market = l.Market__c 
    join #SUBMARKET b on b.Submarket = l.Submarket__c
    join #Segment s on s.Segment = l.Segment__c

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You need to use the table alias in the UPDATE. Also, I think you need left join:

UPDATE  l   
SET Specialty = CASE WHEN   (l.Segment__c is not NULL)  THEN    s.Specialty
                    WHEN    (l.Segment__c is NULL)      THEN    b.Specialty
                    WHEN    (l.Submarket__c is NULL)    THEN    m.Specialty 
                    ELSE s.Specialty 
                END 
FROM #LIST l 
    left join #MARKET m on m.Market = l.Market__c 
    left join #SUBMARKET b on b.Submarket = l.Submarket__c
    left  #Segment s on s.Segment = l.Segment__c;

Otherwise, the NULL values will filter out rows, because they do not match in the join.

Upvotes: 1

Related Questions