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

Reputation: 71

How to update a column 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: 

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 3 values: 'TypeA', 'TypeB' and '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 are not consider about TypeC, just 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 will become like this:

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

Upvotes: 3

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

One method is to aggregate the second table before JOINing:

UPDATE a
    SET settings = (CASE WHEN num_a > 0 AND num_b = 0 THEN 1
                         WHEN num_b > 0 THEN 2
                         ELSE 3
                    END)
FROM tablsA a JOIN
     (SELECT b.name,
             SUM(CASE WHEN type = 'TypeA' THEN 1 ELSE 0 END) as num_a,
             SUM(CASE WHEN type = 'TypeB' THEN 1 ELSE 0 END) as num_B
      FROM tableB b
      GROUP BY b.name
     ) b
     ON a.name = b.name

Upvotes: 0

nitr0n
nitr0n

Reputation: 26

In this case, I see more than one option.

You could consider normalize those tables and add foreign key with cascading the updates.

You could replace one of those tables just a view and map the values; it depends which table can be updated. If both receiving updates this solution may only work if you use indexed views.

If you have different or overlapping settings on different types , also maybe with dependency's from users, you might need an mapping table where you map Type to Settings.

Also it’s possible, to use triggers for those things to ensure consistency, but you should prefer a proper table layout with correct foreign keys.

It may also help to give more details about your use case.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311843

Sounds like you need an update join statement:

UPDATE a
SET    settings = CASE 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') -- just a precaution

Upvotes: 2

Related Questions