An Update query from 2 tables which don't have the same ID to be mapped

Question

So I have a STG table and in that I have a flag field which needs to be updated to 1 just for few employees who are in the particular job post and the other employees values should be unchanged.

I have given a sample data the actual consists of 100+ columns so included which are common

STG Table

Post Table

So to identify the employees who are in that post I have to fetch the value from a different table which has the identification logic has job_post like '%Specialist%' and the setid ='C65' from the post_tbl so the values can't be mapped between these two tables because they don't have any same values or primary key. So can you please help me write a update query with if above condition is true then update the flag value to 1.

I tried:

update STG_Table
set Flag = 1
form STG_Table STG inner join Post_Table P
on STG.job_post = P.job_post 
where upper(P.descr) like '%Specialist%'
and P.setid = 'C65';

Expected O/P:

So, can someone please guide me on the following.

Answer 1

If possible you should fix your table design.

Some suggestions

• add a column such as stg_table_id on the Post_Table table.
• keep id integers only if possible (not like you have in the Post_Table) , integers are more performant on indexes , join conditions.

Please for further questions use text only (as I have posted below) not images.

Regarding your question an options is to use like on the join condition.

Consider your data example

create table STG_Table(
  empid int,
  name varchar(15),
  emp_rcd tinyint,
  company varchar(10),
  job_post varchar(35),
  flag int );

insert into STG_Table values
(123,'John',1,'ACT','General',1),(234,'Mary',0,'WAC','Physician',2),
(345,'Jeff',0,'L&D','FTH Specialist',2),(456,'Bill',1,'ACT','Nurse',1),
(567,'Bob',1,'WAC','CWR Specialist',2);


create table Post_Table(
  setid varchar(5),
  Manager varchar(15),
  key_job int,
  job_post varchar(35) );

insert into Post_Table values
('S23','Belen',1,'General'),('J34','Ram',2,'Physician'),
('C65','Ken',0,'FTH Specialist'), ('E75','Mill',2,'Nurse'),
('C65','Bob',1,'CWR Specialist');

---

If we use

select st.empid,st.name,st.emp_rcd,st.company,st.job_post,st.flag 
from STG_Table st
inner join Post_Table pt  on pt.job_post  like concat ('%', st.job_post, '%')
where pt.setid = 'C65' ;

Result

empid   name    emp_rcd company   job_post     flag
 345    Jeff      0      L&D    FTH Specialist  2
 567    Bob       1      WAC    CWR Specialist  2

---

Now, lets transform it in an update clause

update STG_Table st
inner join Post_Table pt  on pt.job_post  like concat ('%', st.job_post, '%')
set Flag = 1
where setid = 'C65' ;

Result

empid   name    emp_rcd   company   job_post        flag
 123    John       1       ACT       General          1
 234    Mary       0       WAC       Physician        2
 345    Jeff       0       L&D       FTH Specialist   1
 456    Bill       1       ACT       Nurse            1
 567    Bob        1       WAC       CWR Specialist   1

---

Note

An index on setid might speed things up, but in larger data you might face some performance issues because of like '%%' usage.