CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Vũ Trần
Vũ Trần

Reputation: 71

How to update a column from a table base on another column's value from another table

I have a table A, with 2 main columns: Name and Settings. The Settings column will have 3 values: 1, 2 or 3.

For ex:

table A:

Name     Settings
Andre        1
Andre        1
Betty        3
Charles      1
Charles      1

Note that 1 Name can only have 1 Settings: 1, 2 or 3.

I have another table B, with 2 main columns: Name and Type. The Type column consists of many values, like: 'TypeA', 'TypeB', 'TypeC'......

For ex:

table B:

Name       Type
Andre      TypeA
Andre      TypeA
Andre      TypeC
Betty      TypeB
Betty      TypeB
Betty      TypeD
Charles    TypeB
Charles    TypeA
Charles    TypeA
.....

Note that 1 Name can have multiple same or not same Type. And we're just considering about TypeA and TypeB.

Now I want to update the Settings column from table A using the 'Type' column from table B, if:

The result from table A should become like this:

Name     Settings
Andre        1
Andre        1
Andre        1
Betty        2
Charles      3
Charles      3

How can I do it?

Upvotes: 1

Views: 266

Answers (2)

LukStorms
LukStorms

Reputation: 29667

The update can join to a sub-query that counts those types per name for TableB.
Then the new "Settings" can be calculated based on those totals.

UPDATE a
SET a.Settings = (CASE
                  WHEN b.TotalTypeA > 0 AND b.TotalTypeB > 0 THEN 3
                  WHEN b.TotalTypeB > 0 THEN 2
                  WHEN b.TotalTypeA > 0 THEN 1
                  ELSE 0
                  END)
FROM TableA a
LEFT JOIN 
(
  SELECT Name, 
  COUNT(CASE WHEN Type = 'TypeA' THEN 1 END) TotalTypeA,
  COUNT(CASE WHEN Type = 'TypeB' THEN 1 END) TotalTypeB
  FROM TableB 
  GROUP BY Name
) b 
ON b.Name = a.Name;

A test on RexTester here

Upvotes: 1

GMB
GMB

Reputation: 222582

I believe that an aggregated correlated subquery might get the job done, like:

UPDATE ta
SET Settings = (
    SELECT
        CASE 
            WHEN MAX(tb.Type) = 'TypeA' AND MIN(tb.Type) = 'TypeA' THEN 1
            WHEN MAX(tb.Type) = 'TypeB' AND MIN(tb.Type) = 'TypeB' THEN 2
            ELSE 3
       END
    FROM TableB tb
    WHERE tb.Name = ta.Name AND Type in ('TypeA', 'TypeB')
    GROUP BY tb.Name
)
FROM TableA ta

In this demo on DB Fiddle with your sample data, here is the content of TableA after the UPDATE was executed:

Name    | Settings
:------ | -------:
Andre   |        1
Andre   |        1
Betty   |        2
Charles |        3
Charles |        3

Upvotes: 1

Related Questions