CODEWITHSUNDEEP
sqlsql-serversql-updatejoin
shweta
shweta

Reputation: 319

complex sql query to update main table

i have 3 tables 1)main_table 2)Flag_table 3)emp_flagdetails

main_table structure

emp_no   hod_no  emp_name              flgType
E51397  E44417  Asha V  
E42342  E44417  Shaikh Faiz Ahmed   
E06636  E44417  Balu  K U

in the above table i kept flgtype column blank to update later now i have Flag_table structure as follow

FlagId  FlagCategory    FlagType
1         BM            BRML12B
2         BM            BRMM12B
3         BM            BRMRMB
4         BM            BRMCMB
5         BM            BRMZM
6         VH            BRML12V
7         VH            BRMM12V
8         VH            BRMRMV
9         VH            BRMCMV

emp_flagdetails structure is a follow

ecode   flag
E44417  BRML12B
E42342  BRMRMB
E06636  BRMZM
E51397   BRML12B

this is my tables structure,now my query is i want to update flgtype coloumn of main_table with flagcategory column of Flag_table ..in such way that if empno from main_table is present in emp_flagdetails table then we will check the flag column of emp_flagdetails for that matching employee and then we get this flag column value and we will find this value in flag_table if it is present in the flag_table ,we will update main_table flgtype column with flagcategory column value....so the output will be as follow

emp_no   hod_no  emp_name              flgType
E51397  E44417  Asha V                 BM
E42342  E44417  Shaikh Faiz Ahmed      BM
E06636  E44417  Balu  K U              BM

please help me to write the query

Upvotes: 3

Views: 3018

Answers (3)

Axy
Axy

Reputation: 1

update main_table
set flagtype= (select a.flagcategory 
    from (    select m.empno, f.flagcategory
                 from main_table m,emp_flagdetails e , flag_table f  
                 where m.empno=e.ecode
                  and e.eflag=f.flagtype) a
    where  main_table.empno=a.empno) ;

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

UPDATE m
SET m.flgType = f.FlagCategory
FROM main_table m
 JOIN emp_flagdetails fd
  ON fd.ecode = m.emp_no   
 JOIN flag_table f
  ON f.FlagType = fd.flag;

Upvotes: 2

DB_learner
DB_learner

Reputation: 1026

update main_table m, (select a.emp_no, b.flagcategory from emp_flagdetails a, flag_table b 
where a.flag = b.flagtype) s set m.flgtype = s.flagcategory where m.emp_no = s.emp_no

Upvotes: 1

Related Questions