CODEWITHSUNDEEP
sql-server
user3267270
user3267270

Reputation: 33

Update two columns values in a table based on another column value in different table

I have tbl1 contain m_code field, I need to update p_code and e_code fields in tbl2 with certain values. For example if m_code = S then I want to set P_code to 1 and e_code to 10. I have wrote a select statement to show how I would like to display the field values based on different value codes in tbl1. I need help with writing the query..

select 
    case 
    when e.m_code = 'S' then s.P_CODE = 1 and s.E_CODE = 10
    when e.m_code = 'H' then s.P_CODE = 2 and s.E_CODE =5
    when e.m_code = 'E' then s.P_CODE = 3 and s.E_CODE =6
    when e.m_code = 'P' then s.P_CODE = 8 and s.E_CODE = 2
    when e.m_code ='R' then s.P_CODE = 3 and s.E_CODE =1 
    end
    from
    tbl1 e
       join tbl2 s on e.field = s.field
   where s.m_code in ('S','H','E', 'P', 'R')

Upvotes: 1

Views: 197

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24410

update s
set p_code = x.p
, e_code = x.e
from tbl2 s
inner join tbl1 e 
    on e.field = s.field
inner join
    (
        select 'S' x, 1 p, 10 e
        union select 'H', 2, 5
        union select 'E', 3, 6
        union select 'P', 8, 2
        union select 'R', 3, 1
    ) x
    on x.x = e.m_code
where s.m_code in ('S','H','E', 'P', 'R')

SQL Fiddle: http://sqlfiddle.com/#!6/c5357/3

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

Update s 
  SET s.P_CODE = CASE  
                    when e.m_code = 'S' then 1 
                    when e.m_code = 'H' then 2 
                    when e.m_code = 'E' then 3 
                    when e.m_code = 'P' then 8 
                    when e.m_code = 'R' then 3 
                 END

     ,s.E_CODE = CASE  
                    when e.m_code = 'S' then 10
                    when e.m_code = 'H' then 5 
                    when e.m_code = 'E' then 6 
                    when e.m_code = 'P' then 2 
                    when e.m_code = 'R' then 1 
                 END
from tbl1 e join tbl2 s 
on e.field = s.field
where s.m_code in ('S','H','E', 'P', 'R')

Upvotes: 3

Related Questions