sql server - How to update information in another table? -

i have 2 tables in database shown in screenshot.

there common columns values present in tbcompanyagent of in tblicence licensenumber, licenseissuedate , licenseexpirationdate. after complex sql querires , business requirement, columns stateissuedlicensenumber, stateissuedlicenseissuedate , statedissuedlicenseexpirationdate , columns licencenumber, dateissued , expirationdate in tblicence merged single column licensenumber, licenseissuedate , licenseexpirationdate in tbcompanyagent.

also values in stateissuedlicensenumber, stateissuedlicenseissuedate , statedissuedlicenseexpirationdate same same group of records identified group id column licencetype.

actually there 1 resident entry licences stored in stateissuedlicensenumber, stateissuedlicenseissuedate , statedissuedlicenseexpirationdate columns , identified resident column 1 value.

non resident licences stored in licencenumber, dateissued , expirationdate 0 value.

my question if user try make updation in tblicence should reflected on tbcompanyagent. have written procedure below lines of code.

 alter proc [dbo].[prcompanyagentupdate]  (  @newlicencenumber nvarchar(50),  @newdateissued datetime,  @newexpirationdate datetime,  @licencenumber nvarchar(50),  @dateissued datetime,  @expirationdate datetime,  @stateissuedlicencenumber nvarchar(50),  @stateissueddateissued datetime,  @stateissuedexpirationdate datetime  )    declare @cdateissued datetime;  declare @cexpirationdate datetime;   declare @cstateissueddateissued datetime;  declare @cstateissuedexpirationdate datetime;   set @cdateissued= convert(char(10),@dateissued, 126);  set @cexpirationdate= convert(char(10), @cexpirationdate, 126);  set @cstateissueddateissued= convert(char(10), @stateissueddateissued, 126);  set @cstateissuedexpirationdate= convert(char(10), @stateissuedexpirationdate, 126);    update  [tbcompanyagent]   set    licencenumber = @newlicencenumber,   licenceissuedate = @newdateissued,   licenceexpirationdate =@newexpirationdate     licencenumber in (select distinct licencenumber    tblicence licencenumber= @licencenumber ,          stateissuedlicensenumber= @stateissuedlicencenumber   , licenceissuedate = @cdateissued , stateissuedlicenseissueddate =    @cstateissueddateissued ,   stateissuedlicenseexpirationdate = @cstateissuedexpirationdate) 

it not updating information in tbcompanyagent. please me!!!

first execute below query , see if getting record or not. if getting record please verify once.

select l.licencenumber, l.licenceissuedate, l.licenceexpirationdate [tbcompanyagent] ca inner join tblicence l on ca.licencenumber = l.licencenumber l.licencenumber  = @licencenumber , l.stateissuedlicensenumber = @stateissuedlicencenumber , l.licenceissuedate = @cdateissued , l.stateissuedlicenseissueddate = @cstateissueddateissued , l.stateissuedlicenseexpirationdate = @cstateissuedexpirationdate 

if above query giving desired result, want update in other table remove select statement , use update statement this.

update ca set ca.licencenumber = l.licencenumber, ca.licenceissuedate = l.licenceissuedate,   ca.licenceexpirationdate = l.licenceexpirationdate [tbcompanyagent] ca inner join tblicence l on ca.licencenumber = l.licencenumber l.licencenumber  = @licencenumber , l.stateissuedlicensenumber = @stateissuedlicencenumber , l.licenceissuedate = @cdateissued , l.stateissuedlicenseissueddate = @cstateissueddateissued , l.stateissuedlicenseexpirationdate = @cstateissuedexpirationdate