question

antonyjoseph-5093 avatar image
0 Votes"
antonyjoseph-5093 asked antonyjoseph-5093 commented

Query Help to update matching records

Dear Experts

create table PRDT_MAST
(
PRDT_CODE varchar(5),
PRDT_STATUS VARCHAR(5)
);


INSERT INTO PRDT_MAST VALUES('AB01','000');
INSERT INTO PRDT_MAST VALUES('CD01','999');
INSERT INTO PRDT_MAST VALUES('EF01','000');
INSERT INTO PRDT_MAST VALUES('ZA01','999');


CREATE TABLE CUST_PRDT
(
CUST_PRDT_CODE varchar(5),
CUST_PRDT_STATUS VARCHAR(5)
);


insert into CUST_PRDT values('AB01','000');
insert into CUST_PRDT values('CD01','999');
insert into CUST_PRDT values('EF01','000');


create table recon
(
recon_prdt varchar(5),
recode_code varchar(10)
);


if 000 in PRDT_MAST and 000 in CUST_PRDT then update as 102 in recon_code to the respective prdt_code in recon table


similaryly if 999 in PRDT_MAST and 999 in CUST_PRDT then update as 202

Desired result

select * from recon

recon_prdt recon_code
AB01 102
CD01 202
EF01 102
ZA01 202

Please help experts

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It seems to insert data to recon since there is nothing in it, correct?

0 Votes 0 ·

I think that you are the one who may be expert here. That is, you are the one who know the business rules.

Are you saying that if we find 000 in CUST_PRDT, no matter for which CUST_PRDT_CODE, we should add all rows in PRDT_MASTER with 000 in PRDT_MAST to the recon table with code 102? And similar with 202?

That is, if CUST_PRFT would have

insert into CUST_PRDT values('HJ98','000');
insert into CUST_PRDT values('MN13','999');
insert into CUST_PRDT values('GF22','000');

the result would be the same?

Or in other words, why would ZA01 end up in recon?

0 Votes 0 ·

Hi @antonyjoseph-5093

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @antonyjoseph-5093

Please refer below:

One:if 999 in PRDT_MAST and no matter for which in CUST_PRDT then update as 202

  update c
  set c.recode_code= case when a.PRDT_STATUS=000 then 102 when a.PRDT_STATUS=999 then 202 end  
  from recon c 
  inner join PRDT_MAST a on a.PRDT_CODE=c.recon_prdt
  left join CUST_PRDT b on a.PRDT_CODE=b.CUST_PRDT_CODE and a.PRDT_STATUS=b.CUST_PRDT_STATUS

Two: if 999 in PRDT_MAST and 999 in CUST_PRDT then update as 202

   update c
  set c.recode_code= case when a.PRDT_STATUS=000 then 102 when a.PRDT_STATUS=999 then 202 end  
  from recon c 
  inner join PRDT_MAST a on a.PRDT_CODE=c.recon_prdt
  inner join CUST_PRDT b on a.PRDT_CODE=b.CUST_PRDT_CODE and a.PRDT_STATUS=b.CUST_PRDT_STATUS
    
  select * from recon

Output:

 recon_prdt recode_code
 AB01 102
 CD01 202
 EF01 102
 ZA01 202

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered antonyjoseph-5093 commented

Hi @antonyjoseph-5093

Welcome to Microsoft Q&A!

if 999 in PRDT_MAST and 999 in CUST_PRDT then update as 202

You mentioned as above which was conflicted with the data and expected output you provided.

Please refer below two conditions and check whether any of them is helpful to you.

One:if 999 in PRDT_MAST and no matter for which in CUST_PRDT then update as 202

 insert into recon
 select a.PRDT_CODE
 ,case when a.PRDT_STATUS=000 then 102 when a.PRDT_STATUS=999 then 202 end  
 from PRDT_MAST a
 left join CUST_PRDT b on a.PRDT_CODE=b.CUST_PRDT_CODE and a.PRDT_STATUS=b.CUST_PRDT_STATUS
    
 select * from recon

Two: if 999 in PRDT_MAST and 999 in CUST_PRDT then update as 202

 INSERT INTO CUST_PRDT VALUES('ZA01','999');

 insert into recon
 select a.PRDT_CODE
 ,case when a.PRDT_STATUS=000 then 102 when a.PRDT_STATUS=999 then 202 end  
 from PRDT_MAST a
 inner join CUST_PRDT b on a.PRDT_CODE=b.CUST_PRDT_CODE and a.PRDT_STATUS=b.CUST_PRDT_STATUS
    
 select * from recon

Output:

 recon_prdt    recode_code
 AB01    102
 CD01    202
 EF01    102
 ZA01    202

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your replies.

But i want to update in recon table,

already there is record in recon table like below


recon_prdt recon_code
AB01
CD01
EF01
ZA01

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

But i want to update in recon table,

already there is record in recon table like below

That was not clear from your post. Since you posted INSERT statements for the other tables, but not for this one, it was reasonable to assume that it was empty.

You have described some cases, but what if this is the data, what should happen:

INSERT INTO PRDT_MAST VALUES('AB01' ,'000');
INSERT INTO PRDT_MAST VALUES('CD01', '999');
INSERT INTO PRDT_MAST VALUES('EF01', '000');
INSERT INTO PRDT_MAST VALUES('ZA01', '999');
INSERT INTO PRDT_MAST VALUES('MG77', '835');
INSERT INTO PRDT_MAST VALUES('PP55', '999');

insert into CUST_PRDT values('AB01','999');
insert into CUST_PRDT values('CD01','000');
insert into CUST_PRDT values('EF01','347');

INSERT recon (recon_prdt)
   VALUES('AB01', ('CD01'), ('KK22'), ('ED01')


What should happen? You have given some rules, but there are a lot of cases, you are not covering. If we don't know the full rules, how would be able to help you.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.