CODEWITHSUNDEEP
sqlsql-updatevertica
Subhrasish Mallik
Subhrasish Mallik

Reputation: 23

Updating Multiple Columns with multiple Joins

I'm receiving some data from an external source, where the actual values are replaced with ids.

| Serial# | Sex | Married | Income |
------------------------------------
|    1    |  1  |    2    |   1    |
|    2    |  1  |    1    |   3    |
|    3    |  1  |    2    |   2    |

Now, I've a Dimension table with the values for each id:

| Tag     |        Value        | Id |
--------------------------------------
| Sex     |  M                  | 1  |
| Sex     |  F                  | 2  |
| Married |  Y                  | 1  |
| Married |  N                  | 1  |
| Income  |  Less than 100      | 1  |
| Income  |  Between 100 and 1K | 2  |
| Income  |  More than 1K       | 3  |

Now, I want all the ids of the three columns Sex, Married & Income in the First table to be replaced with the values from the First Table.

Also, if a unknown Id comes in, which is not present in the Dimension Table, we would love to update with 'UNKNOWN'

This is just an example. My data contains ~100 such columns. What will be the cheapest and fastest way to achieve that? I don't want to write 100s of UPDATE statements.

Upvotes: 0

Views: 1887

Answers (3)

Madhuri
Madhuri

Reputation: 77

Making few changes to the above code, 

 select i.Serial#, 
    case when sd.Value is not null then sd.Value else 'UNKNOWN' end as sex, 
    case when md.Value is not null then md.Value else 'UNKNOWN' end as married,
    case when id.Value is not null then id.Value else 'UNKNOWN' end as income
    from the_intput_table i
     left outer join dimension sd on sd.id = i.Sex and sd.Tag = 'Sex'
     left outer join dimension md on md.id = i.Married and md.Tag = 'Married'
     left outer join dimension id on id.id = i.Income and id.Tag = 'Income'

Upvotes: 0

user330315
user330315

Reputation:

I don't see the need to update anything, you can join the two tables:

select i."Serial#", 
       sd."Value" as sex, 
       md."Value" as married,
       id."Value" as income
from the_intput_table i
  join dimension sd on sd.id = i."Sex" and sd."Tag" = 'Sex'
  join dimension md on md.id = i."Married" and md."Tag" = 'Married'
  join dimension id on id.id = i."Income" and id."Tag" = 'Income'

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

If you want to update the existing column ID's with its value then it can be achieved using below query:

UPDATE
table1
SET
    table1.Sex = Table_B.col1,
    table1.Married = Table_B.col1,
    table1.Income = Table_B.col1,
FROM table1
     INNET JOIN Dimension  as d1 ON t1.Sex = d1.Id AND d1.Tag = 'Sex'
     INNET JOIN Dimension  as d2 ON t1.Married = d2.Id AND d2.Tag = 'Married'
     INNET JOIN Dimension  as d3 ON t1.Income = d3.Id AND d3.Tag = 'Income'

Upvotes: 0

Related Questions