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

Question

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:

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: 'TypeA', 'TypeB', 'TypeC'......

For ex:

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

Note that 1 Name can have multiple same or not same Type. And we 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 Type column from table B is having the value of 'TypeA', then set all the records, which is having the same Name of both table, to 1 on the Settings column from table A.
• The Type column from table B is having the value of 'TypeB', then set all the records, which is having the same Name of both table, to 2 on the Settings column from table A.
• The Type column from table B is having both of the value of 'TypeA' and 'TypeB', then set all the records, which is having the same Name of both table, to 3 on the Settings column from table A.

The result from table A will become like this:

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

How can I do it? What I've tried so far:

UPDATE a
SET a.Settings = CASE b.Type 
                       WHEN 'TypeA' THEN 1
                       WHEN 'TypeB' THEN 2
                    END
FROM   tablsA a
JOIN   tableB b 
ON a.name = b.name
WHERE  type IN ('TypeA', 'TypeB')

Answer 1

Different ways to solve this. I would add a few columns to make life easier. After that, the update becomes trivial:

USE TEMPDB

CREATE TABLE A (Names VARCHAR (20), Sorts VARCHAR (20), A INT, B INT, AB INT)

INSERT INTO A VALUES 
('Andre'   ,   'TypeA' ,0 ,0, 0),
('Andre'   ,   'TypeA' ,0 ,0, 0),
('Andre'   ,   'TypeC' ,0 ,0, 0),
('Betty'   ,   'TypeB' ,0 ,0, 0),
('Betty'   ,   'TypeB' ,0 ,0, 0),
('Charles' ,   'TypeB' ,0 ,0, 0),
('Charles' ,   'TypeA' ,0 ,0, 0),
('Charles' ,   'TypeA' ,0 ,0, 0) 

UPDATE A
SET A = 1 
WHERE Sorts = 'TypeA'
  AND NOT EXISTS ( SELECT * FROM A AS A2
                WHERE A.Names = A2.Names
                  AND A2.Sorts = 'TypeB' )

UPDATE A
SET B = 1 
WHERE Sorts = 'TypeB'
  AND NOT EXISTS ( SELECT * FROM A AS A2
                WHERE A.Names = A2.Names
                  AND A2.Sorts = 'TypeA' )

UPDATE A
SET AB = 1 
WHERE Sorts = 'TypeA'
  AND  EXISTS ( SELECT * FROM A AS A2
             WHERE A.Names = A2.Names
               AND A2.Sorts = 'TypeB' )

SELECT * FROM A

-- EDIT: Another option is to use a CTE, since the OP can not alter or create tables. Same thing applies, after the extra's are added, you can use the CTE for the update.

CREATE TABLE D (Names VARCHAR (20), Sorts VARCHAR (20))
INSERT INTO D VALUES 
    ('Andre'   ,   'TypeA'),
    ('Andre'   ,   'TypeA'),
    ('Andre'   ,   'TypeC'),
    ('Betty'   ,   'TypeB'),
    ('Betty'   ,   'TypeB'),
    ('Charles' ,   'TypeB'),
    ('Charles' ,   'TypeA'),
    ('Charles' ,   'TypeA'); 

WITH CTE AS 

(
    SELECT *,
         CASE WHEN Sorts = 'TypeA' AND NOT EXISTS ( SELECT * FROM D AS D2
                                                WHERE D.Names = D2.Names
                                                  AND D2.Sorts = 'TypeB' ) THEN 1 ELSE 0 END AS A,
          CASE WHEN Sorts = 'TypeB' AND NOT EXISTS ( SELECT * FROM D AS D2
                                                 WHERE D.Names = D2.Names
                                                   AND D2.Sorts = 'TypeA' ) THEN 1 ELSE 0 END AS B,
          CASE WHEN Sorts = 'TypeB' AND EXISTS ( SELECT * FROM D AS D2
                                             WHERE D.Names = D2.Names
                                               AND D2.Sorts = 'TypeA' ) THEN 1 ELSE 0 END AS AB
    FROM D
)

SELECT * FROM CTE 

Answer 2

Try This

select * into #table1 from ( SELECT 'Andre' AS Name ,1 AS Setting UNION ALL SELECT 'Andre' ,1 UNION ALL SELECT 'Betty' ,3 UNION ALL SELECT 'Charles' ,1 UNION ALL SELECT 'Charles' ,1 )XX

select * into #table2 from ( SELECT 'Andre' AS Name ,'TypeA' AS Type UNION SELECT 'Andre' ,'TypeA' UNION SELECT 'Andre' ,'TypeC' UNION SELECT 'Betty' ,'TypeB' UNION SELECT 'Betty' ,'TypeB' UNION SELECT 'Charles' ,'TypeB' UNION SELECT 'Charles' ,'TypeA' UNION SELECT 'Charles' ,'TypeA' )YY

UPDATE XX SET XX.Setting = CASE WHEN EXISTS(
SELECT
1 FROM #table2 YY WHERE YY.Name = XX.Name AND YY.Type = 'TypeA'

                                  EXCEPT 

                                  SELECT  
                                      1
                                  FROM
                                      #table2 YY
                                  WHERE 
                                      YY.Name = XX.Name
                                      AND YY.Type = 'TypeB'
                                  ) THEN 1
                  WHEN EXISTS(            
                                  SELECT  
                                      1
                                  FROM
                                      #table2 YY
                                  WHERE 
                                      YY.Name = XX.Name
                                      AND YY.Type = 'TypeB'

                                  EXCEPT 

                                  SELECT  
                                      1
                                  FROM
                                      #table2 YY
                                  WHERE 
                                      YY.Name = XX.Name
                                      AND YY.Type = 'TypeA'
                                  ) THEN 2
                  WHEN EXISTS(            
                                  SELECT  
                                      1
                                  FROM
                                      #table2 YY
                                  WHERE 
                                      YY.Name = XX.Name
                                      AND YY.Type = 'TypeA'

                                  INTERSECT 

                                  SELECT  
                                      1
                                  FROM
                                      #table2 YY
                                  WHERE 
                                      YY.Name = XX.Name
                                      AND YY.Type = 'TypeB'
                                  ) THEN 3
              END                                                       FROM  #table1 XX

Answer 3

Here's my approach! I have updated TableA thrice for three different type.

See the following code:

UPDATE TableA SET TableA.Settings = 1
WHERE Exists(
  SELECT 1 FROM TableB B
  WHERE TableA.Name = B.Name
  AND B.Type = 'TypeA'
)

UPDATE TableA SET TableA.Settings = 2
WHERE Exists(
  SELECT 1 FROM TableB B
  WHERE TableA.Name = B.Name
  AND B.Type = 'TypeB'
)

UPDATE TableA SET TableA.Settings = 3
WHERE Exists(
  SELECT 1 FROM TableB B1
  INNER JOIN TableB B2
  ON(B1.Name = B2.Name AND B1.Type != B2.Type)
  WHERE TableA.Name = B1.Name
  AND ((B1.Type = 'TypeA' AND B2.Type = 'TypeB')
    OR (B1.Type = 'TypeB' AND B2.Type = 'TypeA'))
)

Here is the working demo!

Hope, this will help you.

Answer 4

WITH CTE
AS (
    SELECT NAME
        ,COUNT(DISTINCT TYPE) CNO
    FROM TABLEB
    WHERE TYPE <> 'TYPEC'
    GROUP BY NAME
    )
UPDATE A
SET Settings = CASE 
        WHEN T.Type = 'TypeA'
            AND C.CNO = 1
            THEN 1
        WHEN T.Type = 'TypeB'
            AND C.CNO = 1
            THEN 2
        WHEN C.CNO = 2
            THEN 3
        END
FROM TABLEA AS A
INNER JOIN CTE AS C ON A.NAME = C.NAME
INNER JOIN (
    SELECT DISTINCT NAME
        ,TYPE
    FROM TABLEB
    WHERE TYPE <> 'TYPEC'
    ) T ON A.NAME = C.NAME